Geht das… Merge: Bedingtes Insert, Update oder Delete

1. Nicht alle Operationen
2. ⚡Mehrdeutigkeiten werden nicht immer gemeldet
3. Nicht alle Syntax-Varianten
4. ⚡Wendet mehrere Regeln an
5. Keine Views oder Unterabfragen als Quelle

SQLs Merge führt Insert-, Update- oder Delete-Operationen mittels Wenn-Dann-Regeln durch. Das ist nützlich, um den Inhalt einer Tabelle auf den letzten Stand zu bringen. Anstatt eine Delete- und eine Update- und eine Insert-Anweisung auszuführen,0 genügt eine einzige Merge-Anweisung, die alles erledigt. Beinahe alles. Leider gibt es ein Szenario, das durch Standard-SQLs Merge nicht abgedeckt ist, sodass man erst recht zwei Anweisungen braucht. Aber fangen wir von vorne an …

Inhalt:

1. Die Basis-Syntax
2. Die When…Then-Regeln
3. When…And-Bedingungen
4. Unlogische Fehler
5. Die Sichtbarkeit von Spaltennamen
6. Not Matched by Source: Extrazeilen löschen
7. Ein JSON-Dokument als Quelle
8. Weiterführende Links
9. Ergänzende Kompatibilität
10. Normative Referenzen

Die Basis-Syntax

Jede Merge-Anweisung muss die Zieltabelle (Into-Klausel), eine Quelle (Using-Klausel) und eine Join-Bedingung (On-Klausel) angeben.

MERGE
 INTO <Ziel>    [[AS] <neuer Ziel-Name>]
USING <Quelle>  [[AS] <neuer Quell-Name>]
   ON <Join-Bedingung>
      <When…Then-Regeln>

Während das Ziel meist der Name einer Tabelle ist,1 kann die Quelle alles sein, was eine Tabelle liefert – auch Unterabfragen oder Tabellenfunktionen wie json_table. Quelle und Ziel können wie in der From-Klausel umbenannt werden.✓✗

1. Nicht mit json_table, aber mit ähnlichen Funktionen, die das jeweilige System zur Verfügung stellt

Die When…Then-Regeln

Auf die Basis-Syntax folgen die When…Then-Regeln. Genauer gesagt sind es When [Not] Matched…Then…-Regeln, weil jede Regel entweder für den Fall gilt, dass die On-Klausel für eine Quellzeile passende Zeilen im Ziel findet (when matched) oder eben nicht (when not matched). Falls im Ziel passende Zeilen gefunden werden – egal wie viele –, können diese mit Update oder Delete bearbeitet werden. Wenn es keine passende Zeile im Ziel gibt, kann ein Insert ausgelöst werden.

1. Kein Update auf Spalten, die in der On-Klausel genannt werden
2. Nicht zu verwechseln mit then update … delete [where]

Das folgende Beispiel zeigt den typischen Anwendungsfall für Merge: Das Synchronisieren eines Tabelleninhalts mit neueren Daten, die von einem externen System bereitgestellt werden. Im Beispiel ist das externe System das Frontend eines Webshops, das die Wunschliste eines Benutzers zur Verfügung stellt. Die Datenbank speichert den Inhalt aller Wunschlisten in der Wish_Lists-Tabelle (user_id, product_id, qty)2. Wenn ein Benutzer seine Wunschliste bearbeitet, übergibt das Frontend die bearbeitete Liste an Merge, welches die notwendigen Operationen identifiziert und ausführt. Vorläufig gehen wir davon aus, dass das Frontend die bearbeitete Wunschliste eines Benutzers in der Tabelle my_wish_list (product_id, qty) ablegt. Weiter unten werden wir direkt ein JSON-Dokument verarbeiten.

MERGE
 INTO wish_lists
USING my_wish_list
   ON wish_lists.user_id    = ?
  AND wish_lists.product_id = my_wish_list.product_id
 WHEN NOT MATCHED THEN INSERT (user_id, product_id, qty)
                       VALUES (      ?, product_id, qty)
 WHEN     MATCHED THEN UPDATE SET qty = my_wish_list.qty

Die Merge-Anweisung benennt zuerst die Ziel-Tabelle wish_lists, dann die Quelle my_wish_list. Die On-Klausel schränkt die Zieltabelle auf die Zeilen des entsprechenden Benutzers ein – wobei der User_Id-Wert mittels Bind-Parameter (?) übergeben wird. Weiters nutzt die On-Klausel die Product_Id, um die verbliebenen Zeilen der Tabellen wish_lists jenen in my_wish_list zuzuordnen.

Am einfachsten versteht man das anhand eines Beispiels. Nehmen wir an, dass es in der Wish_Lists-Tabelle einen Eintrag für ein bestimmtes Produkt X gibt. Im Wesentlichen sucht die On-Klausel dazu eine passende Zeile in der Ziel-Tabelle wish_lists. Wenn es dort keine Zeile mit passenden User_id- und Product_Id-Werten gibt, greift die When Not Matched-Regel, sodass eine entsprechende Zeile eingefügt wird (Insert). Falls jedoch passende Zeilen gefunden wurden, führt die When matched-Regel ein Update auf die Qty-Spalte dieser Zeilen durch.3 Das passiert, falls der Benutzer den Artikel schon vorher auf der Wunschliste hatte.

Die Anweisungen nach dem Schlüsselwort Then folgen im Wesentlichen der Syntax, die auch die eigenständigen Kommandos verwenden – allerdings etwas abgekürzt. Allen voran gibt man keine Zieltabelle an – die Into-Klausel der Merge-Anweisung legt das Ziel für alle Operationen fest. Weiters werden die Kommandos im Kontext einer einzelnen Quellzeile und der dazugehörigen Zielzeilen ausgeführt. Daher akzeptieren Update und Delete keine Where-Klausel✓✗ – sie wissen auch so, welche Zeilen gemeint sind. Zu guter Letzt ist die Insert-Syntax auf eine einzelne Zeile aus einer Values-Klausel beschränkt. Man kann in Merge also weder insert…select noch mehrzeilige Values-Klauseln nutzen.

Beachte, dass Standard-SQLs Merge-Anweisung von der Quelle gesteuert wird. Jede Quellzeile wird gegen die When…Then-Regeln geprüft. Zeilen in der Ziel-Tabelle, die auf Basis der On-Klausel zu keiner Quellzeile gehören, werden ignoriert. Im Beispiel ist das Ergebnis der On-Klausel für andere Benutzer (user_id) niemals wahr. Die Merge-Anweisung wird diese Zeilen in der Zieltabelle daher nicht ändern. Das ist für diesen Anwendungsfall auch gut so – schließlich soll nur die Wunschliste eines Benutzers aktualisiert werden. Andererseits kann das auch zum Problem werden: Wenn dieser Benutzer ein Produkt aus der Wunschliste entfernt, sollte es auch aus der Wish_Lists-Tabelle entfernt werden. Das wäre zumindest die Idee hinter einer Synchronisierung. Leider hat Standard-SQL keine bequeme Syntax für den Umgang mit Extrazeilen im Ziel. Aber das sehen wir uns später an.

Bevor wir uns die erweiterte Syntax ansehen, noch ein genereller Hinweis. Merge ändert jede Zielzeile maximal einmal.4 Falls einer Zielzeile durch die On-Klausel mehreren Quellzeilen zugeordnet werden, bricht die Merge-Anweisung mit einem Fehler✓✗ ab.5 Das vermeidet die ungünstige Situation, dass die Reihenfolge, in der die Quellzeilen verarbeitet werden, den Effekt von Merge ändert. Im Beispiel oben entspricht das dem Fall, dass my_wish_list zwei Einträge für ein und dasselbe Produkt hat.6 Falls die Stückzahlen (Qty) nicht zufällig dieselben sind, macht die Ausführungsreihenfolge einen Unterschied.

When…And-Bedingungen

Die When-Bedingungen sind keineswegs auf die Schlüsselworte [not] matched beschränkt. Nach einem And kann man eine beliebig komplexe Bedingung anhängen. Damit kann man das Beispiel um eine weitere When Matched-Regel erweitern, die einen Eintrag löscht, wenn die Stückzahl (Qty) gleich 0 ist.

…
 WHEN MATCHED AND my_wish_list.qty = 0 THEN DELETE
 WHEN MATCHED THEN UPDATE SET qty = my_wish_list.qty

Durch die zusätzliche Bedingung greift die erste When Matched-Regel nur, wenn die Stückzahl auf 0 gesetzt wurde. In diesem Fall wird die zugehörige Zielzeile durch die Delete-Anweisung gelöscht. Die übrigen Matched-Zeilen lösen wie zuvor ein Update aus. Das Update könnte man allerdings auch an eine zusätzliche Bedingung knüpfen: wish_lists.qty <> my_wish_list.qty. Das verhindert das Update, wenn die Stückzahl unverändert ist. Dadurch wird die Erkennung von Änderungen vollständig umgesetzt. Das ist insbesondere vorteilhaft, wenn Trigger oder Change Data Capture (CDC) im Spiel sind, kann aber generell Performancevorteile bringen.

Falls mehrere When-Bedingungen wahr sind, löst nur die erste davon eine Aktion aus. Die Reihenfolge der When…Then-Regeln ist also wichtig.7 Genau wie beim Case-Ausdruck. Quellzeilen, die keine einzige When-Bedingung erfüllen, lösen keine Aktion aus.

1. Alternative: Where-Klausel. Siehe „Nennenswerte Erweiterungen“

Beachte, dass es Produkte gibt, die zwar eine And-Bedingung annehmen, aber nur eine Regel pro Matched/Not Matched-Fall akzeptieren.

Unlogische Fehler

Merge kann mit seinen Fehlermeldungen auch für Verwirrung sorgen. Das liegt daran, dass Standard-SQLs Merge zuerst alle notwendigen Maßnahmen (Insert, Update, Delete) identifiziert, bevor es sie in einem zweiten Schritt durchführt. In anderen Worten werden zuerst alle When-Bedingungen ausgewertet, bevor irgendwelche Änderungen vorgenommen werden. Für die letzte Ergänzung zum Wunschlisten-Beispiel bedeutet das, dass Zeilen, die von der Delete-Anweisung gelöscht wurden, keinesfalls eine When Not Matched-Regel auslösen können – auch nicht, wenn diese Regel in der Merge-Anweisung später steht. Wenn mit der Ausführung von Insert, Update oder Delete begonnen wird, wurden bereits alle Entscheidungen getroffen.8

Wenn man dieses Verhalten auf das folgende Szenario anwendet, kann es zu einem „unlogischen“ Fehler kommen:9 Eine Merge-Anweisung wertet die When Not Matched-Regeln aus und identifiziert eine Zeile, die eingefügt werden muss. Inzwischen, noch bevor Merge die Zeile einfügt, fügt eine andere Transaktion eine Zeile mit demselben Primärschlüsselwert ein und führt ein Commit durch. Wenn Merge dann die Aktionen durchführt, für die es sich zuvor entschieden hat, schlägt das Insert wegen der Verletzung des Primärschlüssels fehl. Die Fehlermeldung kann aber überraschen: When Not Matched Then Insert sollte eigentlich nicht greifen, wenn es schon eine entsprechende Zeile gibt. Merge entscheidet aber zuerst, was es alles tun muss, und schreitet erst danach zur Tat. In der Zwischenzeit kann sich die Lage geändert haben. Die Constraints und Transaktionsisolation bleiben jedenfalls gewahrt – auch wenn das zu einem überraschenden Fehler führt.

Die unten erwähnten „Upsert“-Anweisungen, die von manchen Systemen angeboten werden, könnten sich in solchen Situationen weniger überraschend verhalten.

Die Sichtbarkeit von Spaltennamen

Ich habe bereits auf einige Unterschiede zwischen den alleinstehenden Insert-, Update- und Delete-Anweisungen und deren Varianten in Merge hingewiesen. Es gibt aber auch einen etwas subtileren Unterschied: Die alleinstehenden Anweisungen sehen immer nur die Spaltennamen der Zieltabelle. Bei Merge sind jedoch zwei Tabellen und deren Spaltennamen sichtbar. Im nächsten Beispiel sind vier Stellen markiert, bei denen unklar ist, welche Spalte x gemeint ist, falls es diese Spalte in beiden Tabellen gibt.

MERGE
 INTO ziel
USING quelle
   ON x = x ⓵
WHEN MATCHED AND x > 0 ⓶ THEN UPDATE SET x = x + 1 ⓷
WHEN NOT MATCHED         THEN INSERT (x) VALUES (x) ⓸

Natürlich kann man diese Mehrdeutigkeiten durch das Qualifizieren mit dem entsprechenden Tabellennamen auflösen – z. B. quelle.x. Man erwartet aber auch, dass Mehrdeutigkeiten zu einem Fehler führen – und dass es keinen Fehler gibt, wenn alles eindeutig ist. Es stellt sich aber heraus, dass diese durchaus berechtigten Erwartungen nicht von allen Systemen erfüllt werden.

1. Siehe „Nennenswerte Erweiterungen“
2. Der Name der Zieltabelle ist auch auf der linken Seite sichtbar, was jedoch in der Standard-SQL-Syntax nicht vorgesehen ist: update set <ziel>.<Spalten-Name> = …
3. ⚡Kein Fehlerabbruch: Vom System abhängige Auflösung der Mehrdeutigkeit
4. Spaltennamen, die in beiden Tabellen existieren, können zu einem Fehlerabbruch führen, obwohl nur die Quelle sichtbar sein sollte. In jenen Systemen kann man das durch eine Qualifikation lösen, was dann allerdings nicht mehr Standard-SQL ist

Problematisch ist insbesondere, dass manche Systeme die mit ⓷ gekennzeichnete Mehrdeutigkeit nicht melden. Es macht schließlich einen Unterschied, ob der Wert in der Zieltabelle um 1 erhöht wird (ziel.x + 1) oder ob er auf den Wert quelle.x + 1 gesetzt wird. Das sind zwei grundverschiedene Dinge, aus denen sich manche Systeme einfach eines aussuchen.

Not Matched by Source: Extrazeilen löschen

Standard-SQL hat keine Syntax zum Bearbeiten von Zeilen in der Zieltabelle, die keine Entsprechung in der Quelle haben. Das gilt aber nur für Standard-SQL. Es gibt nämlich Systeme, die eine solche Syntax anbieten – über die Standard-Syntax hinausgehend.10

Zur Demonstration können wir das Wunschlisten-Beispiel fortsetzen. Konkret soll auch der Fall abgedeckt werden, dass ein Produkt von der Wunschliste gestrichen wird. Eine Zeile in Wish_Lists soll also gelöscht werden, wenn es in der Quelle keine Entsprechung mehr gibt. Die nicht-standardisierte Bedingung dafür ist when not matched by source.

…
 WHEN NOT MATCHED THEN INSERT (user_id, product_id, qty)
                       VALUES (      ?, product_id, qty)
 WHEN NOT MATCHED BY SOURCE THEN DELETE
…

Die neue Bedingung trifft auf alle Zielzeilen zu, die keine passende Quellzeile haben. Die Then-Klausel löscht sie. Alle davon. Das sind zusätzlich zu den gelöschten Einträgen des aktuellen Benutzers auch alle Einträge aller anderen Benutzer! Beachte die On-Klausel: Die Bedingung auf User_Id war dazu gedacht, Einträge anderer Benutzer unberührt zu lassen. Das funktioniert, wenn nur Quellzeilen Aktionen auslösen können. Mit When Not Matched By Source können aber auch Zielzeilen alleine Aktionen auslösen. Da Einträge anderer Benutzer niemals passende Quellzeilen haben, werden sie alle gelöscht. Die Anforderung, die Wunschlisten anderer Benutzer nicht zu ändern, wird sträflich missachtet.

Man kann die Anforderung aber direkt in die When-Bedingung schreiben.

WHEN NOT MATCHED BY SOURCE AND user_id = ? THEN DELETE

Durch die And-Bedingung greift die Regel nur noch für Einträge des angegebenen Benutzers.

Neben When Not Matched By Source gibt es auch die By Target-Variante, die der Standard-SQL Bedingung When Not Matched ohne jegliche By-Angabe entspricht.

Auch mit den Mitteln von Standard SQL kann Merge die entfernten Einträge eines Benutzers löschen. Dazu nutzt man eine Unterabfrage als Quelle, die die zu löschenden Einträge zusätzlich ausgibt – z. B. mittels Full Outer Join oder Union. Wenn man diese Zeilen markiert, indem man zum Beispiel die Spalte qty mit Null befüllt, kann eine When Matched And-Bedingung genau diese Zeilen löschen. Mit diesem Ansatz geht aber beinahe die gesamte Eleganz der Merge-Anweisung verloren, sodass eine separate Delete-Anweisung eventuell die bessere Option ist. Daher überlasse ich diese Übung den Lesern und zeige zum Abschluss lieber ein besseres Beispiel dafür, was man sonst noch als Merge-Quelle verwenden kann.

Ein JSON-Dokument als Quelle

Bei dem Wunschlisten-Beispiel schwingt von Anfang an die Frage mit, was der Vorteil von Merge ist, wenn das Frontend die My_Wish_List-Tabelle vorher befüllen muss?11 Da drängt sich doch der Gedanke auf, dass die entsprechenden Delete-, Update- und Insert-Anweisungen einfacher wären. Insbesondere wenn ein ORM-Tool die Anweisungen ohnehin automatisch generiert. Mit diesem Beispiel kann man aber auch zeigen, wie die Kombination verschiedener SQL-Funktionen interessante Möglichkeiten bietet.

Stellen wir uns also der Realität, dass die aktuelle Wunschliste vom Web-Frontend eigentlich als JSON-Dokument zur Verfügung gestellt wird – vielleicht wie dieses:

[
 {"product_id":42 ,"qty":1}
,{"product_id":123,"qty":2}
]

Die Funktion json_table kann dieses Dokument in eine tabellarische Form überführen, die als Quelle für Merge taugt. Dafür muss man den Funktionsaufruf lediglich in die Using-Klausel schreiben.

…
USING JSON_TABLE( ?
                , '$[*]'
          COLUMNS ( product_id INT PATH '$.product_id'
                  , qty        INT PATH '$.qty'
                  )
                ) my_wish_list
…

Wenn das JSON-Dokument über den Bind-Parameter (?) übergeben wird, erledigt SQL den Rest.

Weil wir gerade beim Kombinieren von Funktionen sind, betrachten wir kurz noch einen ähnlichen, dennoch völlig anderen Anwendungsfall: Die Verarbeitung einer neuen Bestellung. Der Anwendungsfall ist ähnlich, weil dabei wohl ein ähnliches JSON-Dokument zum Einsatz kommt. Er ist aber aus drei Gründen auch völlig anders: (1) Die bestellten Produkte müssen nur eingefügt werden – es ist also kein Fall für Merge, sondern ein einfaches Insert; (2) Es muss aber auch eine zweite Tabelle befüllt werden – die Tabelle für die Bestellung als solches, nicht die bestellten Produkte. Man braucht also zwei Insert-Anweisungen mit verschiedenen Zielen.12 Auch da ist Merge keine Hilfe. Das SQL-Feature T495 „Combined data change and retrieval“ allerdings schon. Damit können Insert-, Update-, Delete- und auch Merge-Anweisungen als Unterabfragen in Select-Anweisungen eingebettet werden, sodass man sie beliebig kombinieren kann. Eine einzige SQL-Anweisung kann damit alle Datenbankoperationen durchführen, die für eine neue Bestellung notwendig sind. Aber das ist Stoff für einen anderen Artikel.

An dieser Stelle möchte ich nur noch den dritten substanziellen Unterschied zwischen dem Aktualisieren einer Wunschliste und einer neuen Bestellung nachreichen: (3) Das tabellarische (relationale) Ablegen der Daten ist bei einer Bestellung definitiv vorteilhaft für die weitere Verarbeitung. Bei einer Wunschliste ist das nicht unbedingt der Fall. Man könnte das Wunschlisten-JSON einfach wie es ist abspeichern. Falls man jemals den Inhalt der Wunschlisten auswerten möchte, kann man mit den entsprechenden SQL-Funktionen (wie json_table) natürlich auch in einer Abfrage auf den Inhalt des JSON-Dokuments zugreifen. SQL schreibt nicht vor, wie die Daten abzulegen sind. Im Gegenteil: SQL unterstützt viele Ansätze.13 Wir können … nun ja, wir müssen je nach Anwendungsfall den passendsten wählen.

1. Ohne merge • Nur wenn die Anweisung auf oberster Ebene ein Select ist: insert into … select … final from (delete from …)
2. Ohne merge

Über den Autor

Markus Winand gibt auf modern-sql.com Einblick in SQL und zeigt, wie es von verschiedenen Systemen unterstützt wird. Zuvor machte er use-the-index-luke.com, was er noch immer wartet. Markus kann als Trainer, Sprecher und Berater auf winand.at engagiert werden.

Weiterführende Links

• insert … on conflict do … (PostgreSQL, SQLite)

• insert … on duplicate key … (MariaDB, MySQL)

• insert … log errors … (Oracle DB)

• update … from (BigQuery, PostgreSQL, SQL Server, SQLite)

• T495, „Combined data change and retrieval“ (DB2 (LUW))

• Data-Modifying Statements in With (PostgreSQL)

Ergänzende Kompatibilität

Nennenswerte Erweiterungen

1. ⚡Vom System abhängige Auflösung der Mehrdeutigkeit

Fehlercodes (SQLSTATE)

1. Falsche SQLSTATE-Class (nicht 21)
2. Richtige SQLSTATE-Class 21, aber Unterklasse ist nicht 000

Syntax-Varianten

Um maximale Kompatibilität zu erreichen, muss man beachten, dass nicht alle Produkte alle Varianten unterstützen.

1. Nur, wenn man die Zieltabelle nicht umbenennt

Normative Referenzen

Die Merge-Anweisung ist in ISO/IEC 9075-2:2016 als optionale Funktionen F312, „MERGE statement“, F313, „Enhanced MERGE statement“ und F314, „MERGE statement with DELETE branch“ definiert.