Das versteckte SQL-Server-Gem: UPDATE from SELECT
3 einfache Möglichkeiten, UPDATE from SELECT zu nutzen, um sich die Arbeit zu erleichtern
Sie waren wahrscheinlich schon einmal in dieser Situation: Sie mussten die Daten in einer Tabelle aktualisieren und dabei Informationen verwenden, die in einer anderen Tabelle gespeichert waren. Ich treffe oft Leute, die noch nichts von der leistungsstarken Lösung UPDATE from SELECT gehört haben, die SQL Server für dieses Problem bietet. Tatsächlich habe ich mich mit diesem Problem eine ganze Weile herumgeschlagen, bevor ich von diesem Juwel erfahren habe.
In den folgenden Zeilen zeige ich Ihnen drei Tricks, die mir das Leben bei vielen Gelegenheiten einfacher gemacht haben. Dazu benötigen wir zunächst zwei Tabellen:
Wenn Sie das Skript nachvollziehen möchten, können Sie es hier herunterladen: The Hidden SQL Server Gem – UPDATE from SELECT.sql
Entwickeln Sie Ihr UPDATE aus einem SELECT
Nun, da wir die Umgebung eingerichtet haben, lassen Sie uns eintauchen, wie das funktioniert. Bevor ich Ihnen die Mehrtabellenlösung zeige, möchte ich Ihnen die einfachste Form der UPDATE FROM-Syntax demonstrieren und Ihnen einen einfachen Trick zeigen, mit dem Sie Ihre UPDATE-Anweisungen ganz einfach entwickeln können, indem Sie zunächst eine SELECT-Anweisung schreiben und diese dann durch das Löschen von zwei Zeichen in ein Update verwandeln. Interessiert?
Die Tabelle dbo.Orders enthält eine Spalte is_archived. Diese wird verwendet, um Bestellungen, die älter als 90 Tage sind, zu archivieren. Allerdings haben wir die zusätzliche Regel, dass Bestellungen, die noch nicht bezahlt oder versendet wurden, nicht archiviert werden können. (In diesem Beispiel hat die is_archived-Spalte keine physikalische Wirkung. Lesen Sie meinen SQL Server Pro-Artikel Using Table Partitions to Archive Old Data in OLTP Environments, wenn Sie daran interessiert sind, wie Sie die Spalte is_archived in einen tatsächlichen Archivierungsschalter verwandeln können.)
Eine SELECT-Anweisung, die die archivierbaren Datensätze zurückgibt, würde wie folgt aussehen:
FROM dbo.Orders AS O
WHERE O.order_date < DATEADD(DAY,-90,SYSDATETIME())
AND O.is_paid = 1
AND O.is_shipped = 1;
Nun fügen wir noch ein wenig Magie hinzu:
SELECT *, — */
is_archived = 1
FROM dbo.Orders AS O
WHERE O.order_date < DATEADD(DAY,-90,SYSDATETIME())
AND O.is_paid = 1
AND O.is_shipped = 1;
Dies ist immer noch eine einfache SELECT-Anweisung. Wir haben nur zwei Kommentare und eine zusätzliche Spalte hinzugefügt. Diese zusätzliche Spalte verwendet die unübliche Alias-Syntax name = value, die der gebräuchlicheren Syntax value AS name entspricht.
Das Schöne daran ist, dass ich diesen Select in eine syntaktisch korrekte UPDATE-Anweisung verwandeln kann, indem ich einfach die beiden Bindestriche vor dem UPDATE-Schlüsselwort entferne:
SELECT *, — */
is_archived = 1
FROM dbo.Orders AS O
WHERE O.order_date < DATEADD(DAY,-90,SYSDATETIME())
AND O.is_paid = 1
AND O.is_shipped = 1;
Dies aktualisiert die Tabelle dbo.Orders genau wie eine UPDATE dbo.Orders SET… Anweisung, da die Tabelle dbo.Orders Tabelle als O aliasiert ist und das UPDATE auf denselben O-Alias verweist.
Hinzufügen eines JOIN zur UPDATE-Anweisung
Die Frage, die uns hier beschäftigt hat, war, wie wir Daten in einer Tabelle verwenden können, um eine andere Tabelle zu aktualisieren. Wäre es nicht schön, wenn wir einfach „JOIN“ sagen könnten? Gute Nachrichten: Mit der obigen Syntax können wir genau das tun.
Kürzlich wurde der Tabelle dbo.Customers eine Spalte order_count hinzugefügt. Unsere Aufgabe ist es nun, diese Spalte auf der Grundlage der tatsächlichen Bestellungen jedes Kunden korrekt zu bewerten. Wir beginnen wieder, indem wir zunächst einen Select schreiben:
SELECT *, — */
Bestellmenge = OA.cnt
FROM dbo.Customers AS C
JOIN(
SELECT O.customer_id,
COUNT(1) cnt
FROM dbo.Orders AS O
GROUP BY O.kunden_id
)OA
ON C.kunden_id = OA.kunden_id;
Wenn die SELECT-Anweisung die korrekten Ergebnisse liefert, ist es einfach, sie auf UPDATE umzustellen:
SELECT *, — */
Auftrags_count = OA.cnt
FROM dbo.Customers AS C
JOIN(
SELECT O.customer_id,
COUNT(1) cnt
FROM dbo.Orders AS O
GROUP BY O.customer_id
)OA
ON C.customer_id = OA.customer_id;
Durch die Verwendung des Alias C weiß SQL Server, dass er die Tabelle dbo.Customers zu aktualisieren, während die notwendigen Informationen aus anderen Tabellen, auf die in der Anweisung verwiesen wird, herangezogen werden.
UPDATE mit einer CTE verwenden
Wenn CTEs Ihr Ding sind, können Sie damit sogar noch einen Schritt weiter gehen. Solange SQL Server leicht feststellen kann, was Sie zu aktualisieren beabsichtigen, können Sie tatsächlich ein CTE direkt mit einer sehr ähnlichen Syntax „UPDATE“:
(
SELECT O.customer_id,
COUNT(1) cnt
FROM dbo.Orders AS O
GROUP BY O.customer_id
),
customer_order_counts AS
(
SELECT
C.customer_id,
C.name,
C.order_count,
OC.cnt new_order_cnt
FROM dbo.Customers AS C
JOIN order_counts AS OC
ON C.customer_id = OC.customer_id
)
UPDATE COC SET /*
SELECT *, — */
order_count = COC.new_order_cnt
FROM customer_order_counts AS COC;
Das Einzige, was Sie mit keiner der obigen Anweisungen machen können, ist, Daten in zwei Tabellen gleichzeitig zu aktualisieren.
Ein Wort der Vorsicht
Mit dieser Syntax haben wir eine sehr mächtige Möglichkeit, UPDATE-Anweisungen zu schreiben, die Daten aus mehr als einer Tabelle benötigen. Seien Sie jedoch vorsichtig, dass Sie keinen Code schreiben, der ein zufälliges Verhalten zeigt. Schauen Sie sich diese UPDATE-Anweisung an:
Genauso wie das hervorgehobene SELECT einen Kunden mit mehreren Bestellungen mehrfach zurückgibt, würde das UPDATE jeden Kunden fröhlich mehrfach aktualisieren, wobei jedes Mal die vorherige Änderung überschrieben wird. Nur die letzte Änderung würde bestehen bleiben.
Das Problem dabei ist, dass es keine Garantie gibt, in welcher Reihenfolge das geschieht. Die Reihenfolge ist abhängig vom gewählten Ausführungsplan und kann sich jederzeit ändern. Während die obige Anweisung also dazu führen könnte, dass in Ihren Tests tatsächlich das Datum der letzten Bestellung geschrieben wird, wird sie wahrscheinlich in einer anderen Reihenfolge ausgeführt, sobald die Anweisung auf eine große Datenmenge trifft. Dies wird zu schwer zu debuggenden Problemen führen. Achten Sie also auf diese Möglichkeit, wenn Sie Ihre UPDATE FROM SELECT-Anweisungen schreiben.