Wie man Unterabfragen in INSERT-, UPDATE- und DELETE-Anweisungen verwendet

Wussten Sie, dass Unterabfragen auch in UPDATE-, INSERT- und DELETE-Anweisungen verwendet werden können? Unterabfragen in SQL sind wie Bausteine, die in einer Vielzahl von Anweisungen verwendet werden können, nicht nur in SELECT. Wenn Sie Unterabfragen in SELECT nützlich finden, lesen Sie diesen Artikel, um herauszufinden, wie nützlich sie in anderen Anweisungen sein können. Sie werden im Handumdrehen in der Lage sein, viel komplexere und leistungsfähigere SQL-Abfragen zu erstellen!

Wenn Sie Ihr Wissen über Unterabfragen auffrischen möchten, empfehle ich Ihnen den interaktiven SQL-Übungssatz Kurs. Er enthält über 80 Übungen zu Unterabfragen und anderen anspruchsvollen SELECT Konstruktionen.

Startpunkt: Ein kurzer Überblick über Subqueries

Beginnen wir mit einer kurzen Erinnerung daran, was eine Subquery ist. Da Unterabfragen am häufigsten in SELECT Anweisungen verwendet werden, werden wir ein Beispiel für eine einfache Unterabfrage in einer SELECT Anweisung betrachten.

Wir können eine Unterabfrage als eine Abfrage innerhalb einer anderen Abfrage definieren. Unterabfragen werden am häufigsten in der WHERE -Klausel von SELECT -Anweisungen verwendet, können aber auch in verschiedenen anderen Klauseln verwendet werden, z. B. WHERE, FROM und HAVING.

Schauen wir uns die Datenbank an, die wir als Beispiel verwenden werden. Stellen Sie sich vor, Sie sind der Besitzer eines Weinladens und haben eine einfache Datenbank mit 3 Tabellen, um den Betrieb des Ladens zu verwalten.

Die erste Tabelle ist winedie die von Ihnen verkauften Produkte speichert, mit dem Namen, dem Preis, der Anzahl der vorrätigen Flaschen usw. für jeden Wein. Die zweite ist orderin der die Bestellungen unserer Kunden gespeichert werden, unter anderem mit dem Namen des bestellten Weins und der bestellten Menge.

wine

order

Nehmen wir an, wir möchten eine Liste der Weine erhalten, für die wir noch nie eine Bestellung erhalten haben. Die Abfrage sieht wie folgt aus:

SELECT  name,
FROM    wine
WHERE   name NOT IN ( SELECT wine_name FROM order )

Die Unterabfrage gibt die Namen aller Weine zurück, für die wir Bestellungen erhalten haben. Die äußere Abfrage ermittelt dann mit dem Operator NOT IN die Namen der Weine, die nie in einer Bestellung enthalten waren. SQL ist so einfach wie mächtig! Wenn Sie einige Konzepte der Unterabfragen auffrischen möchten, empfehle ich Ihnen den Kurs SQL für Anfänger wo Sie einen vollständigen Abschnitt über Subqueries finden.

Erste Station: Verwendung von Unterabfragen in INSERT-Anweisungen

Lassen Sie uns nun eine Subquery in einer INSERT -Anweisung verwenden. Die Idee ist, die komplette Ergebnismenge einer Subquery oder einer SELECT -Anweisung in eine Tabelle einzufügen. Nehmen wir als Beispiel an, wir möchten Rechnungen für alle Weinbestellungen erstellen, die wir im Laufe des Tages erhalten haben. Unten sehen Sie eine Teilansicht unserer invoice Tabelle:

Nehmen wir an, das heutige Datum ist der 28. Juni 2020, und wir möchten die Datensätze der Rechnungen einfügen, die mit den heutigen Bestellungen verbunden sind. Wir können die folgende SELECT verwenden, um die Daten für die Rechnungen zu generieren:

SELECT  o.date, 
        o.client_id, 
        o.quantity * w.price as amount, 
        o.quantity || ’ bottles of ‘ || o.wine_name,
        o.order_id
FROM    order o 
JOIN    wine w ON w.name = o.wine_name
WHERE   o.date = ‘2020-06-28’

Das Ergebnis dieser Abfrage sehen Sie unten:

Dies ist genau das, was wir in die Tabelle einfügen wollen invoice.

Wenn wir einfach eine INSERT Klausel vor der Abfrage hinzufügen, können wir das Ergebnis der Abfrage in die Tabelle einfügen wineeinfügen, wie wir im folgenden Beispiel sehen können:

INSERT INTO invoice (date, client_id, amount, wine_name, order_id)
SELECT  o.date, 
        o.client_id, 
        o.quantity * w.price as amount, 
        o.quantity || ’ bottles of ‘ || o.wine_name,
        o.order_id
FROM    order o 
JOIN    wine w ON w.name = o.wine_name
WHERE   o.date = ‘2020-06-28’

Beachten Sie das Folgende:

• Wir müssen die Spalten der Tabelle explizit benennen invoice Tabelle, in die wir einfügen, explizit benennen.
• Die Spalten in der Liste SELECT müssen in der gleichen Reihenfolge stehen wie die Spalten in der Tabelle.
• Wir haben die Spalte invoice_id weggelassen, damit die Datenbank standardmäßig den nächsten Wert mithilfe eines Sequenzgenerators auswählen kann.

Nach dem Ausführen der INSERT enthält die Tabelle invoice die neuen Rechnungsdatensätze für die Bestellungen von heute enthalten. Dies ist unten zu sehen, wobei die neuen Datensätze rot markiert sind:

Nehmen wir an, dass wir an einem bestimmten Tag versehentlich die INSERT zweimal ausgeführt haben und unsere Kunden deshalb für jede Bestellung zwei Rechnungen erhalten haben. Wir wollen nicht, dass sich dieser Fehler in Zukunft wiederholt! Um dies zu verhindern, fügen wir der Anweisung INSERT eine Unterabfrage hinzu, um festzustellen, ob bereits eine Rechnung mit der gleichen order_id existiert. Im Folgenden sehen Sie die neue Version der Abfrage INSERT. Die am Ende hinzugefügte Unterabfrage identifiziert die Rechnungen, die bereits existieren, und die äußere Abfrage verwirft sie mit Hilfe des NOT IN Operators.

INSERT INTO invoice (date, client_id, amount, wine_name, order_id)
SELECT order.date, 
       order.client_id, 
       o.quantity * w.price as amount, 
       o.quantity || ’ bottles of ‘ || o.wine_name,
       o.order_id
FROM   order o 
JOIN   wine ON wine.name = order.wine_name
WHERE  order.date = ‘2020-06-28’
  AND  o.order_id NOT IN (SELECT order_id 
                           FROM invoice i 
                           WHERE i.order_id=o.order_id )

Wenn Sie Ihre Kenntnisse in der Verwendung der Anweisungen INSERT, UPDATE und DELETE verbessern möchten, empfehle ich Ihnen den Kurs Wie man Daten in einer Datenbank in SQL ändert wo Sie mehrere Beispiele für diese Anweisungen sehen können.

Zweiter Halt: Unterabfragen in UPDATE-Anweisungen

Wie SELECT kann auch die Anweisung UPDATE eine Unterabfrage an mehreren Stellen oder Klauseln enthalten. In einer UPDATE sind die beiden Klauseln, in denen Unterabfragen am häufigsten verwendet werden, SET und WHERE.

In der Klausel SET wird der neue Wert für die Spalte definiert, die durch die UPDATE geändert wird. Wir können eine Unterabfrage verwenden, um diesen neuen Wert zu erhalten, der aus einer beliebigen Tabelle oder einer gültigen Unterabfrage ausgewählt werden kann, solange wir nur einen Datensatz mit nur einer Spalte für jeden zu aktualisierenden Datensatz zurückgeben. Der Datentyp der Spalte, die von der Unterabfrage zurückgegeben wird, muss derselbe sein wie der der Spalte, die geändert wird.

Erstellen wir eine UPDATE, um unseren Weinflaschenbestand auf dem neuesten Stand zu halten. Am Ende des Tages werden wir eine UPDATE ausführen, um die heute verkauften Flaschen zu erfassen. Der Code sieht dann so aus:

UPDATE wine w
SET stock = stock - (
                     SELECT SUM (quantity)
                     FROM order
                     WHERE date = CURRENT_DATE AND order.wine_name = w.name
                    )

Wenn wir in der UPDATE keine WHERE Klausel verwenden, würden wir am Ende alle Datensätze in der Tabelle Wein ändern, einschließlich der Datensätze für die Weine, die wir heute nicht verkauft haben. Die Unterabfrage gibt ein NULL für jeden Wein zurück, der heute nicht verkauft wurde, und wir würden fälschlicherweise SET die Bestandsspalte auf NULL setzen, da das Ergebnis des Ausdrucks "stock - NULL" NULL ist. Wir müssen dies korrigieren.

Es gibt zwei Ansätze für diese Lösung. Der erste besteht darin, den Ausdruck SUM(quantity) so zu ändern, dass er statt NULL eine Null zurückgibt. Dazu müssen wir einfach die Funktion COALESCE verwenden, etwa so:

UPDATE wine w
SET stock = stock - (
                     SELECT coalesce(SUM (quantity), 0)
                     FROM order
                     WHERE date = CURRENT_DATE AND order.wine_name = w.name
                    )

Der zweite Ansatz besteht darin, eine Unterabfrage in der Klausel WHERE hinzuzufügen, um nur die Weine zu ändern, die heute bestellt wurden, und den Ausdruck SUM(quantity) unverändert zu lassen. Die folgende Abfrage zeigt diesen Ansatz:

UPDATE wine w
SET stock = stock - (
                     SELECT SUM (quantity)
                     FROM order
                     WHERE date = CURRENT_DATE AND order.wine_name = w.name
                    )
WHERE w.name IN (SELECT order.wine_name FROM order WHERE date = CURRENT_DATE)

Diese UPDATE ist nicht ideal: Diese SET Klausel verwendet eine korrelierte Unterabfrage. Eine korrelierte Subquery ist eine Abfrage, die die Datenbank viele Male ausführen muss - ein Mal für jede Zeile, die in der Tabelle geändert wird. In unserem Beispiel ruft die Abfrage die SUM(quantity) für jeden heute verkauften Wein ab. Obwohl korrelierte Unterabfragen sehr leistungsfähig sein können, sollten sie aus Gründen der optimalen Vorgehensweise nach Möglichkeit vermieden werden. Hier können wir die korrelierte Unterabfrage vermeiden, indem wir eine FROM -Klausel in der UPDATE verwenden, wie wir unten sehen können:

UPDATE wine w
SET stock = stock - subquery.total_in_orders
FROM ( SELECT wine_name, sum( order_quantity ) AS total_in_orders  
       FROM   order 
       WHERE  date = TODAY 
       GROUP BY wine_name 
     ) subquery
WHERE w.name = subquery.wine_name 

Als zusätzliche Ressource empfehle ich den Artikel Anleitung für Anfänger zur SQL-Subquery, in dem Sie das ABC der Subqueries anhand von Beispielen unterschiedlicher Komplexität lernen können.

Dritte Station: Unterabfragen in DELETE-Anweisungen

Mit der Anweisung DELETE können Unterabfragen nur innerhalb einer WHERE-Klausel verwendet werden. Angenommen, wir möchten die Datensätze von Weinen ausschließen, für die wir in den letzten 6 Monaten keine Bestellung erhalten haben. Wir können eine Unterabfrage erstellen, die die in den letzten 6 Monaten verkauften Weine zurückgibt, und dann die Datensätze, die wir entfernen möchten, in der Tabelle wine Tabelle entfernen möchten, indem wir den Operator NOT IN verwenden. Schauen wir uns an, wie SQL dies macht:

DELETE FROM wine w
WHERE name NOT IN ( SELECT wine_name 
                    FROM   order 
                    WHERE  date >= CURRENT_DATE - interval ‘6 Months’ 
                  )

Nehmen wir an, wir wollen die Weine eliminieren, für die die Gesamtbestellungen in den letzten 6 Monaten weniger als 10 Einheiten betrugen. Die Anweisung DELETE sieht dann wie folgt aus:

DELETE FROM wine w
WHERE 10 > ( SELECT SUM(quantity) 
                    FROM   order o 
                    WHERE  o.wine_name = w.name 
                      AND  date >= CURRENT_DATE - interval ‘6 Months’
                  )

Hier gibt die Unterabfrage die Menge der in den letzten 6 Monaten bestellten Flaschen für jeden Wein zurück. Durch den Vergleich dieser Menge mit 10 können wir feststellen, ob ein bestimmter Wein ausgeschlossen werden sollte.

Am Ende der Tour der Unterabfragen angelangt

Unterabfragen sind wie Bausteine in SQL. Wir haben gesehen, wie sie an verschiedenen Stellen verwendet werden können, z. B. in SELECT Anweisungen oder in beliebigen Änderungsanweisungen wie INSERT, UPDATE und DELETE. Wir haben gesehen, wie wir eine Subquery in verschiedenen Klauseln verwenden können und dass es verschiedene Möglichkeiten gibt, Subqueries in INSERT, UPDATE und DELETE Anweisungen zu verwenden. Wenn Sie mehr über Subqueries erfahren möchten, empfehle ich Ihnen den Artikel Subquery vs. CTE: Eine SQL-Fibel, in dem eine Art von Subquery namens CTE mit vielen Beispielen und Details erklärt wird. Schließlich gibt es noch zwei Kurse mit vielen nützlichen Informationen über Subqueries mit vielen Beispielen: SQL für Anfänger wo Sie einen kompletten Abschnitt über Subqueries finden, und Wie man Daten in einer Datenbank in SQL ändert der ein Kurs für Fortgeschrittene ist. Auf geht's, verbessern Sie Ihre SQL-Kenntnisse!