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 12: Synchronisiertes Update und Update in Transaktion

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
Schließen Sie den in Beispiel 6 eingefügten Wareneingang ab, indem Sie die gelieferten Artikelmengen dem jeweiligen Lager zubuchen. Danach muss der Status des Wareneingangs von 1 (erfasst) auf 2 (abgeschlossen) geändert werden.

Transkript

Mit diesem Film möchte ich noch abschließend zwei Updates mit Ihnen üben. Dieses Beispiel verwenden wir, um den Wareneingang, den wir mit Beispiel 6 eingefügt haben, abzuschließen. Das bedeutet, dass wir einerseits die eingefügte Artikelmenge in den jeweiligen Lagern zubuchen, andererseits den Status des Gesamtwareneingangs von "1" steht für "fast" auf "2", "abgeschlossen", ändern. Neben den Wareneingangstabellen benötigen wir dazu auch den Lagerstand. In der Tabelle "Lagerstand" finden wir neben Artikel- und Lagernummer die Menge, in der diese momentan auf Lager ist. Ich habe hier die Artikel, die in der Wareneingangsposition mit dieser Nummer, die wir vorhin eingefügt haben, gejoined, um zu sehen, welchen Bestand wir momentan in den jeweiligen Lagern aufweisen. und wir wollen daraus den neuen Bestand berechnen. Betrachten wir uns kurz den Wareneingang. Der besteht aus einem Satz in der Tabelle "Wareneingang" das ist die Wareneingangsnummer mit dem Datum, Lieferantennummer und der Lieferscheinnummer, und noch mit Status "1". Und diese sechs Positionen mit unterschiedlichen Artikeln und der Menge 12, momentan alle dem Lager "1" zugeordnet. Damit es mal interessanter wird, führe ich noch dieses Update hier aus und ändere das Lager für all jene ab Positionsnummer vier, also die letzten drei von "1" auf "2", weil sie doch in das andere Lager gebracht werden sollen. Ich führe das aus, habe das gleich ohne Transaktion hier gemacht. Das heißt, wäre das die automatische Transaktion, ist das sofort committed hier beim SQL Server, und betrachte mir noch einmal das Ergebnis. Schaut sehr ähnlich aus, nur habe ich jetzt hier Lagernummer "2". Das sind die aktuellen Werte. Das werden wir zubuchen und das soll das Ergebnis sein. In diesem Beispiel müssen Sie also ein synchronisiertes Update über die Wareneingangspositionen zu den Lagerbeständen machen. Achten Sie darauf, dass wir diesmal zwei Kriterien haben, damit die Daten für das synchronisierte Update zusammen gefunden werden. Wir müssen nämlich einerseits Artikelnummern ident haben, als auch die Lagernummer, da ja beide Werte passen müssen, für ein korrektes Update. Das sollten Sie nicht vergessen. Verwenden Sie also ein synchronisiertes Update mit einem Join bei MySQL und dem Microsoft SQL Server und eine korrelierte Unterabfrage, wie wir sie schon in anderen Beispielen für Oracle verwendet haben. Nach dem Update auf den Lagerbestand machen wir noch ein Update auf die Tabelle "Wareneingang", damit wir diesen Stauts von "1" auf "2" setzen können, was gleichbedeutend ist mit dieser Wareneingang ist jetzt abgeschlossen und korrekt. (Musik läuft) 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. Und nun zur Lösung unter diesen drei Systemen. Fangen wir mit dem Microsoft SQL Server an. Das Update benötigt einen Join, wir joinen in einem "FROM" hinter der "SET"-Klausel einerseits den Lagerstand, andererseits die Wareneingangsposition. Und zwar über zwei Spalten, nämlich über die Artikelnummer, für den Lagerstand, dem ich "l" als Aliasnamen gegeben habe, "l.artnr =", und "w" ist gleich der Wert für die Wareneingangspositionen ist gleich "w.artikel", so heißt die Spalte dort. Zusätzlich soll der "lagnr" das ist die Lagernummer in der Lagerstandstabelle, gleich sein der Spalte "Lager", so heißt sie, im Wareneingang und wir wollen nur den Wareneingang mit dieser Wareneingangsnummer berücksichtigen. Beachten Sie, dass bei Ihnen unter Umständen hier eine andere Nummer zu verwenden ist, je nachdem, wie Sie die vorhergehenden Beispiele ausgeführt haben. Ich starte also eine Transaktion und führe nun dieses Update aus. Wir haben ja gesehen, dass vorhin nur sehr geringen Mengen auf Lager gewesen sind. Nun sehen wir, dass sechs Zeilen von dieser Anweisung betroffen sind und im Anschluss mache ich gleich das Update auf den Wareneingang, um den Status auf "2" zu ändern. Damit ist dokumentiert, dass diese Werte jetzt in das jeweilige Lager eingebucht worden sind. Das Ergebnis kontrollieren wir, indem ich hier den Lagerstand noch einmal mit den Wareneingangspositionen joine, damit wir nur genau jene Waren- und Artikelwerte betrachten und nicht den kompletten Inhalt der Tabelle "Lagerstand". Und wir sehen jetzt, dass die Mengen, die jeweils über 12 sind, von "0" auf "12" erhöht worden sind, von "1" auf "13" und von "2 "auf "14". Das heißt, die Mengen sind hier eingebucht worden. Ich beende die Transaktion wieder mit einem "COMMIT". Damit ist dieser Vorgang abgeschlossen. Sehr ähnlich finden wir das Ganze auch unter MySQL gelöst. Wir haben hier dieselbe Ausgangsituation, auch hier habe ich noch momentan in den Wareneingangspositionen, wie wir hier sehen, alles auf Lagernummer "1" eingestellt, deshalb ändere ich auch hier, wie beim Microsoft SQL Server zuvor, die Lagernummer auf "2" für alle Positionsnummern größer gleich vier für diese Bestellung. Habe ich ausgeführt und betrachte mir das Ergebnis. Und wir sehen jetzt hier, wieder Lagernummer "1" und "2" sind die Ziellager, Altbestand, Zubuchung und Neubestand nach der Änderung. Hier die Lösung: Mit einem Join, die aus meiner Sicht einfachere Lösung hier unter MySQL, nämlich "UPDATE", "lagerstand l" und gleich danach der Join "INNER JOIN", "wareneingangspositionen", und wie beim Microsoft SQL Server auch hier, über Artikelnummer und Lagernummer. Wir weisen der Menge einerseits den alten Bestand zu plus die Menge, die sich aus dem Wareneingang ergibt. aber "0" für diese Wareneingangsnummer und ich führe diese Anweisung aus. Kurze Kontrolle und ich sehe auch hier, sechs Zeilen sind von dieser letzten Anweisung betroffen. Wahlweise könnten wir das natürlich auch über eine korrelierte Unterabfrage lösen, eins zu eins ident, wie wir es auch gleich bei Oracle kennenlernen. Wir schauen uns das jetzt gar nicht hier im Detail an, dazu wechsle ich dann gleich in den Oracle-Editor. Hier ändere ich jetzt auch den Status für den Wareneingang mit dieser einfachen Anweisung. Eine Zeile haben wir geändert, und ich kontrolliere auch hier das Ergebnis. und wir sehen, dass nun die Lagerstandsmengen angepasst sind. Deshalb beende ich auch hier die Transaktion mit einem "COMMIT". Wie angekündigt, schließen wir das Ganze mit der Oracle-Lösung ab hier brauchen wir ja eine korrelierte Unterabfrage. Die Ausgangssituation ist dieselbe, auch hier finden wir die Mengen und die Wareneingangswerte mit dieser Nummer vor. Auch hier könnte es bei Ihnen ein anderer Wert sein, also kontrollieren Sie das bitte. Auch hier mache ich das Update, um den Positionen "= 4" das Lager "2" zuzuweisen und habe damit diese Werte, wir sehen den Altbestand, Zubuchung und Neubestand. Betrachten wir uns nun hier die korrelierte Unterabfrage. Auch hier passiert das Update nicht über einen Join, sondern über "SET menge = menge +" und hier die korrelierte Unterabfrage. In der korrelierten Unterabfrage verweisen wir auf die Wareneingangspositionen für den Wareneingang mit dieser Nummer und die entsprechende Artikelnummer. Achtung, das müssten wir hier machen: Wir müssten hier unter Umständen Summen bilden, wenn es sein könnte, dass ein und dieselbe Artikelnummer öfter in einem Wareneingang vorkommt. Das ist hier nicht der Fall, deshalb kann ich das ignorieren. Ein Spezialfall sehen wir in der "WHERE"-Klausel. Wir müssen ja nun hier einschränken, dass nur jene Werte upgedatet werden, bei denen sowohl Lagernummer als auch Artikelnummer ident sind. Nachdem das nicht über einen Join passiert, passiert das hier aber über eine sogenannte "parallele Unterabfrage". Das ist eine Sonderform, die unter Oracle und auch unter MySQL möglich ist. Diese Variante, wie Sie hier sehen, wird zum Beispiel vom Microsoft SQL Server nicht unterstützt. Bei einer paarweisen Unterabfrage werden ausnahmsweise auf der linken Seite der "WHERE"-Klausel zwei Spalten in Klammern – das ist wichtig und mit Komma getrennt – angegeben, "(artnr, lagernr)". In der Unterabfrage wird die gleiche Anzahl an Werten in der gleichen Reihenfolge, also "artikel" und "lager", so heißen die Spalten hier, zurückgegeben. Damit müssen beim Vergleich die linken Spalten und rechten Spalten ident sein, damit das zusammenkommt. Und deshalb werden nur jene upgedatet, bei denen Artikel und Lager ident ist, wie in den Wareneingangspositionen. Wenn wir diese Anweisungen ausführen, werden die entsprechenden Artikel-Lager-Kombinationen gefiltert und über die korrelierte Unterabfrage bekommen die Mengen zusätzlich die Menge der Wareneingangsmenge zurück. Damit ich hier nicht "wawi.lagerstand" verwenden muss, den langen Namen zum Referenzieren aus der Unterabfrage, habe ich hier "l" als Aliasnamen vergeben und hier in der Unterabfrage eben auf diesen Wert in der Hauptabfrage verwiesen. Ich führe nun also diese Anweisung aus. Wir sehen, sechs Zeilen sind aktualisiert worden und nachdem ich auch den Status des Wareneingangs hier upgedatet habe, prüfen wir noch abschließend das Ergebnis. Und wir sehen, dass bei den Lagerständen, die Artikel aus dieser Wareneingangspositionsliste enthalten nun die höheren Lagerstände zu erkennen sind. Wir haben also in diesem Beispiel nochmal eine synchronisierte Update-Anweisung gesehen, unter Oracle verwenden wir eine korrelierte Unterabfrage und in dem Fall zum Filtern eine paarweise Unterabfrage. Diese Variante können wir wahlweise wenn wir das möchten, eins zu eins auch unter MySQL einsetzen. Allerdings favorisiere ich unter MySQL die Variante über den Join, weil sie auch vom Umfang der Anweisung her und der Übersichtlichkeit aus meiner Sicht schneller zu lösen ist. Auch ist sie, glaube ich, einfacher realisierbar, weil ich einfach einen Join mache über die zwei Spalten und das entsprechende Update. Beim Microsoft SQL Server müssen wir ohnenhin diese Variante mit dem Update verwenden, da die paarweise Unterabfrage zum Beispiel beim Microsoft SQL Server nicht verwendet werden kann.

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!