Am 14. September 2017 haben wir eine überarbeitete Fassung unserer Datenschutzrichtlinie veröffentlicht. Wenn Sie video2brain.com weiterhin nutzen, erklären Sie sich mit diesem überarbeiteten Dokument einverstanden. Bitte lesen Sie es deshalb sorgfältig durch.

SQL Grundkurs 2: Aufgaben und Lösungen

Beispiel 11: Synchronisiertes Update mit Unterabfrage

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
Setartikel sollen um 10% billiger sein als die Summe ihrer Bestandteile. Nutzen Sie dazu ein synchronisiertes Update mit Unterabfrage.

Transkript

Wenn Sie das Beispiel 9 als sehr anspruchsvoll empfunden haben, und Sie sich mittlerweile bei dem etwas einfacheren Beispiel 10 schon wieder etwas erholt haben, dann ist das Beispiel 11 nun genau das richtige. Worum geht es in diesem Video? Bei diesem Beispiel soll wieder ein synchronisiertes Update über eine Tabelle mit einer Unterabfrage hinweg gemacht werden. Also sehr ähnlich, wie das Beispiel 9. Das bedeutet, Sie können einen ähnlichen Sachverhalt verwenden, um eine derartige Aufgabenstellung noch einmal zu üben Worum geht es in diesem Beispiel? Sie sollen den Set-Artikeln einen Preis geben, der zehn Prozent unter dem Preis oder der Summe der Preise ihrer Bestandteile besteht. Werfen wir einmal, um uns zu orientieren, einen Blick in die Tabelle "Set-Artikel". In der Tabelle "Set-Artikel" sind Sets definiert. In der Spalte "setartnr" finden wir die Hauptnummer des Sets und "teilartnr" liefert uns den einzelnen Bestandteil. Über die Spalte "menge" wissen wir, wie viele von diesem Teil im Set enthalten sind. Beide Artikelnummern, die der Sets als auch die der Teile, finden sich mit einem Preis, mit einem VK-Preis, in der Artikeltabelle. Wenn wir also prüfen, wie momentan die Preise aussehen, indem wir uns das Ganze ansehen, indem wir die Artikeltabelle zweimal mit der Set-Artikeltabelle joinen, sehen wir als aktuellen Preis die Preise für die Artikelnummern 1520 für 1521 und 2112. Jetzt sehen wir, der errechnete Setpreis, bereits berücksichtigt die zehnprozentige Ersparnis, ist natürlich ein ganz anderer Wert. Das heißt, das was hier momentan eingetragen ist, stimmt überhaupt nicht. Über ein Update soll also dieser Preis für diese Artikelnummern zugeordnet werden. Das ist die Aufgabenstellung. Unter MySQL und dem Microsoft SQL Server benötigen Sie dazu ein synchronisiertes Update mit einer Unterabfrage. In der Unterabfrage können wir die Summe der Einzelpreise für eine Set-Artikelnummer bilden. Bei Oracle verwenden Sie eine korrelierte Abfrage, da ein Joinen in einem Update ja nicht möglich ist. (Musik spielt) Pausieren Sie nun das Video und lösen Sie die Aufgabe. Wenn Sie damit fertig sind, fahren Sie mit dem Video fort und sehen Sie sich meine Musterlösung an. Ich hoffe, Beispiel 11 ist Ihnen nun schon leichter gefallen, als Beispiel 9. Betrachten wir uns das Ergebnis einmal hier unter MySQL. Wir sehen hier die Unterabfrage, die kopiert auf die Set-Artikelnummer gejoined mit der Artikeltabelle uns die Summe der Verkaufspreise mal der Setmenge und bereits um zehn Prozent vergünstigt, in dem Fall auf zwei Nachkommastsellen gerundet, ausgibt. Das sind also diese Preise, die wir nun diesen Set-Artikeln zuordnen möchten. Nun verwenden wir ein Update mit einer "JOIN"-Anweisung, bei der hier direkt über die Artikel- und Set-Artikelnummer die Verbindung hergestellt wird. Und wir weisen zu, dass der Verkaufspreis des Artikels gleich dem errechneten Setpreis sein soll. Dieser gibt ich hier über den Aliasnamen in der Unterabfrage. Da es sich hier um einen "INNER JOIN" handelt, werden automatisch nur die Set-Artikel upgedatet, die einen Treffer landen. Das bedeutet, wir benötigen hier keine zusätzliche "WHERE"-Klausel. Wir führen nun diese Anweisung aus und wir sehen, dass drei Zeilen von dieser Anwendung, von diesem Update, betroffen sind. Nun kontrollieren wir das Ergebnis und wir sehen, dass die zuvor ausgerechneten Preise jetzt hier wirklich eingetragen sind. Wir können auch noch einmal unsere Ausgangsanweisung verwenden, dann sehen wir wirklich im Vergleich, dass nun der aktuelle Preis diesem errechneten Setpreis entspricht. Da es uns passt, bestätige ich diese Änderung mit einem "COMMIT". Betrachten wir uns nun das Beispiel unter dem Microsoft SQL Server. Hier haben wir eine sehr ähnliche Situation. Auch hier haben wir die gleiche Ausgangsbasis, der errechnete Setpreis entspricht überhaupt nicht dem aktuellen Preis. Auch hier verwenden wir ein Update mit der identen Unterabfrage. Wir sehen hier die Unterabfrage, die ausgeführt uns die neuen Preise liefert. Auch hier habe ich gruppiert. diese Unterabfrage ist zur Gänze ident, wie unter der MySQL-Lösung. Anders ist hier eigentlich nur der "JOIN". Wir haben hier – ich fahre hier ein wenig herunter, damit wir da wenig mehr sehen – UPDATE, habe hier in der "FROM"-Klausel hinter dem Set den Aliasnamen definiert, kann den gleich, wenn ich möchte statt "wawi.artikel" auch hier in der "UPDATE"-Klausel verwenden, weise den Setpreis zu. Setpreis ist diese berechnete Spalte in der Unterabfrage, und aufgrund des "INNER JOIN" über Artikel-und Set-Artikelnummer werden nur jene upgedatet, die wirklich ein Set sind. Das heißt, wir benötigen hier keine "WHERE"-Klausel, um das Ganze zu lösen. Ich führe also diese Anweisung aus, vorher noch eine Transaktion, wie gewohnt, um es gegebenenfalls auch wieder rückgängig machen zu können. Drei Zeilen haben wir geändert und die Kontrolle zeigt uns jetzt, dass die drei Preise, die wir mittlerweile kennen, jetzt hier korrekterweise eingetragen worden sind. Daher bestätige ich jetzt auch hier die Transaktion mit einem "COMMIT". Also ein "UPDATE", "SET", danach ein "FROM" mit einem "JOIN" das ist die richtige Lösung unter dem Microsoft SQL Server. Betrachten wir noch die Lösung unter Oracle. Auch hier finden wir die Tabelle "Set-Artikel" mit den momentan akutell nicht passenden Preisen vor. Diese Setpreise wollen wir errechnen und zuweisen, so wie wir es bei den anderen schon getan haben. Die Lösung sieht nun bei Oracle etwas anders aus. Bei Oracle kann ein synchronisiertes Update ausschließlich über eine sogenannte "korrelierte Unterabfrage" erfolgen. Es erfolgt hier kein direkter "JOIN" in der "UPDATE"-Anweisung. Das wird nicht unterstützt und das ist auch nicht möglich. Was wir benötigen, ist eine Unterabfrage, die uns den gewünschten Setpreis berechnet. Dazu habe ich hier eine Unterabfrage vorbereitet, die die Set-Artikel mit der Artikeltabelle joint über die entsprechenden Artikelnummern, und dann diesen Preis als Summe, ähnlich wie beim "JOIN" in den beiden anderen, ausrechnet. Warum brauche ich jetzt hier keine Gruppierung? Hier wird eine korrelierte Unterabfrage verwendet, die so aufgebaut ist, dass sie auf eine Spalte in der Hauptabfrage verweist. In der Hauptabfrage habe ich der Tabelle "wawi.artikel" den Aliasnamen "a" gegeben. Hier durch "a.artnr" in der Unterabfrage verweise ich direkt auf den Wert in der Hauptabfrage. Das heißt, bei jedem Artikel wird die Artikelnummer übergeben und die Summe für alle seine Setbestandteile gebildet. Da braucht man keine Gruppierung. Damit wir nicht alle Artikel, sondern nur die Set-Artikel updaten, müssen wir noch filtern. Das mussten wir ja zum Beispiel bei Microsoft SQL Server nicht, da wir direkt den "INNER JOIN" beim "UPDATE" verwendet haben, der von vorneherein jene Daten ändert, wo es eine Zuordnung gibt. Das ist ja hier nicht der Fall. Deshalb benötigen wir eine separate "WHERE"-Klausel, wo wir auf die Artikel filtern, die in der Set-Artikeltabelle vorkommen. Das habe ich jetzt hier in dieser Anweisung gemacht. und hole mir die Set-Artikelnummer hier heraus. Zusätzlich habe ich hier noch gruppiert, damit jede Set-Artikelnummer, auch wenn sie mehrere Bestandteile hat, über diese Anweisung einmal angezeigt wird. Das macht zwar nicht wirklich einen Unterschied hier im Ergebnis, auch nicht in der Performance. Würde dieses jetzt hier Ihnen mehrere liefern aber das ist ein bisschen ein Schönheitsfehler, wenn wir nicht gruppieren. Damit liefert uns die Unterabfrage, wenn ich das jetzt hier so markiere und ausführe, nur genau einmal jede Set-Artikelnummer und das filtern sie mit "IN" und damit werden nur jene Werte geändert. Nun führe ich auch hier unter Oracle diese Anweisung einmal aus, habe jetzt hier die drei Zeilen aktualisiert und mit dieser Anweisung führe ich die Kontrolle durch. Und wir sehen, dass wir nun hier auch die Werte zugeordnet haben, so wie wir es bereits bei den anderen beiden gesehen haben. Wenn ich die obere Anweisung zum Prüfen ausführe, dann sehen wir jetzt, dass Setpreis und aktueller Preis hier übereinstimmen. So wollten wir das. Und nachdem das Ganze passt, kann ich auch hier diese Änderung noch abschließend mit einem "COMMIT" festschreiben. Wir haben also gesehen, dass wir hier unter Oracle wenn wir synchronisierte Updates vornehmen, das nicht direkt mit einem "JOIN" machen können, sondern dass wir eine korrelierte Unterabfrage verwenden. Eine korrelierte Unterabfrage greift in der Unterabfrage auf einen Wert in der Hauptabfrage zu, filtert auf diesen und damit wird hier der korrekte Wert berechnet. Damit haben wir unser Update unter Oracle vorgenommen. Bei Microsoft SQL Server verwenden wir direkt einen "JOIN" über eine "FROM"-Klausel nach einem "SET", um diese synchronisierte Anweisung auszuführen. Und unter MySQL, auch sehr ähnlich, wie unter dem SQL Server, die Syntax lediglich ein wenig anders, dass nämlich der "JOIN" direkt nach dem "UPDATE"-Schlüsselwort hier schon erfolgt. Ich hoffe, dieses Beispiel hat Ihnen nun diese Möglichkeiten mit einer synchronisierten Unterabfrage weiter nähergebracht und Sie konnten das jetzt sauber lösen.

SQL Grundkurs 2: Aufgaben und Lösungen

Vertiefen Sie Ihre SQL-Kenntnisse. In diesem Workshop erhalten Sie zahlreiche Beispielaufgaben, die Sie selbst lösen können. Anschließend zeigt Ihnen der Trainer die Lösung.

7 Std. 41 min (61 Videos)
Derzeit sind keine Feedbacks vorhanden...
 

Dieses Training setzt SQL-Kenntnisse voraus, wie sie beispielsweise in „SQL lernen und anwenden“ vermittelt werden.

Dieser Online-Kurs ist als Download und als Streaming-Video verfügbar. Die gute Nachricht: Sie müssen sich nicht entscheiden - sobald Sie das Training erwerben, erhalten Sie Zugang zu beiden Optionen!

Der Download ermöglicht Ihnen die Offline-Nutzung des Trainings und bietet die Vorteile einer benutzerfreundlichen Abspielumgebung. Wenn Sie an verschiedenen Computern arbeiten, oder nicht den ganzen Kurs auf einmal herunterladen möchten, loggen Sie sich auf dieser Seite ein, um alle Videos des Trainings als Streaming-Video anzusehen.

Wir hoffen, dass Sie viel Freude und Erfolg mit diesem Video-Training haben werden. Falls Sie irgendwelche Fragen haben, zögern Sie nicht uns zu kontaktieren!