MERGE¶

Fügt Werte in einer Tabelle basierend auf Werten in einer zweiten Tabelle oder Unterabfrage ein, aktualisiert und löscht sie. Dies kann nützlich sein, wenn die zweite Tabelle ein Änderungsprotokoll ist, das neue Zeilen (die eingefügt werden sollen), geänderte Zeilen (die aktualisiert werden sollen) und/oder markierte Zeilen (die gelöscht werden sollen) in der Zieltabelle enthält.

Der Befehl unterstützt die Semantik zur Behandlung der folgenden Fälle:

Übereinstimmende Werte (für Aktualisierungen und Löschungen).
Nicht übereinstimmende Werte (für Einfügungen).

Siehe auch:: DELETE, UPDATE

Syntax¶

MERGE INTO <target_table> USING <source> ON <join_expr> { matchedClause | notMatchedClause } [ ... ]

Copy

Wobei:

matchedClause ::=
  WHEN MATCHED [ AND <case_predicate> ] THEN { UPDATE SET <col_name> = <expr> [ , <col_name2> = <expr2> ... ] | DELETE } [ ... ]

Copy

notMatchedClause ::=
   WHEN NOT MATCHED [ AND <case_predicate> ] THEN INSERT [ ( <col_name> [ , ... ] ) ] VALUES ( <expr> [ , ... ] )

Copy

Parameter¶

target_table: Gibt die Tabelle an, die zusammengeführt werden soll.
source: Gibt die Tabelle oder Unterabfrage an, die mit der Zieltabelle verbunden werden soll.
join_expr: Gibt den Ausdruck an, für den die Zieltabelle und die Quelle verbunden werden sollen.

`matchedClause` (für Updates oder Löschungen)¶

WHEN MATCHED ... THEN UPDATE <col_name> = <expr> | DELETE

Gibt die Aktion an, die ausgeführt werden soll, wenn die Werte übereinstimmen.

AND case_predicate

Gibt optional einen Ausdruck an, der, wenn „true“, die Ausführung des übereinstimmenden Falles bewirkt.

Standard: Kein Wert (der übereinstimmende Fall wird immer ausgeführt)

SET col_name = expr [ … ]

Gibt die Spalte in der Zieltabelle an, die aktualisiert oder eingefügt werden soll, und den entsprechenden Ausdruck für den neuen Spaltenwert (kann sich sowohl auf die Ziel- als auch auf die Quellbeziehung beziehen).

In einer einzelnen SET-Unterklausel können Sie mehrere Spalten angeben, die aktualisiert/gelöscht werden sollen.

`notMatchedClause` (für Eingaben)¶

WHEN NOT MATCHED ... THEN INSERT

Gibt die Aktion an, die ausgeführt werden soll, wenn die Werte nicht übereinstimmen.

AND case_predicate

Gibt optional einen Ausdruck an, der, wenn „true“, die Ausführung des nicht übereinstimmenden Falles bewirkt.

Standard: Kein Wert (der übereinstimmende Fall wird immer ausgeführt)

( col_name [ , ... ] )

Gibt optional eine oder mehrere Spalten in der Zieltabelle an, die aktualisiert oder eingefügt werden sollen.

Kein Wert (alle Spalten in der Zieltabelle werden aktualisiert oder eingefügt)

VALUES ( expr [ , ... ] )

Gibt die entsprechenden Ausdrücke für die eingefügten Spaltenwerte an (muss sich auf die Quellbeziehungen beziehen).

Nutzungshinweise¶

Eine einzelne MERGE-Anweisung kann mehrere übereinstimmende und nicht übereinstimmende Klauseln enthalten (d. h. WHEN MATCHED ... und WHEN NOT MATCHED ...).
Jede übereinstimmende oder nicht übereinstimmende Klausel, die die AND-Unterklausel weglässt (Standardverhalten), muss die letzte ihres Klauseltyps in der Anweisung sein (z. B. kann auf eine WHEN MATCHED ...-Klausel keine WHEN MATCHED AND ...-Klausel folgen). Dies führt zu einem nicht erreichbaren Fall, der einen Fehler zurückgibt.

Verhalten bei doppelter Verknüpfung (Join)¶

Deterministische Ergebnisse für UPDATE und DELETE¶

Wenn eine Zusammenführung eine Zeile in der Zieltabelle mit mehreren Zeilen in der Quelle verknüpft, führen die folgenden Verknüpfungsbedingungen zu nicht deterministischen Ergebnissen (d. h. das System kann den Quellwert nicht ermitteln, der zum Aktualisieren oder Löschen der Zielzeile verwendet werden soll):

Es wird eine Zielzeile ausgewählt, die mit mehreren Werten aktualisiert werden soll (z. B. WHEN MATCHED ... THEN UPDATE).
Es wird eine Zielzeile ausgewählt, die sowohl aktualisiert als auch gelöscht werden soll (z. B. WHEN MATCHED ... THEN UPDATE, WHEN MATCHED ... THEN DELETE).

In dieser Situation hängt das Ergebnis der Zusammenführung von dem für den Sitzungsparameter ERROR_ON_NONDETERMINISTIC_MERGE angegebenen Wert ab:

Bei TRUE (Standardwert) gibt die Zusammenführung einen Fehler zurück.
Bei FALSE wird eine Zeile aus den Duplikaten ausgewählt, um die Aktualisierung oder die Löschung durchzuführen. Die ausgewählte Zeile ist nicht definiert.

Deterministische Ergebnisse für UPDATE und DELETE¶

Deterministische Zusammenführungen sind immer fehlerfrei. Eine Zusammenführung ist deterministisch, wenn für jede Zielzeile die folgenden Bedingungen erfüllt sind:

Eine oder mehrere Quellzeilen erfüllen die Klausel WHEN MATCHED ... THEN DELETE, und keine der anderen Quellzeilen erfüllt die WHEN MATCHED-Klausel

OR
Exakt eine Quelle erfüllt eine WHEN MATCHED ... THEN UPDATE-Klausel, und keine anderen Quellzeilen erfüllen WHEN MATCHED-Klauseln.

Dadurch entspricht MERGE semantisch den Befehlen UPDATE und DELETE.

Bemerkung

Um Fehler zu vermeiden, wenn mehrere Zeilen in der Datenquelle (d. h. die Quellentabelle oder Unterabfrage) gemäß der ON-Bedingung mit der Zieltabelle übereinstimmen, verwenden Sie GROUP BY in der Quellklausel, um sicherzustellen, dass jede Zielzeile mit einer (mindestens) Zeile der Quelle verknüpft ist.

Im folgenden Beispiel wird davon ausgegangen, dass src mehrere Zeilen mit demselben k-Wert enthält. Es ist nicht eindeutig, welche Werte (v) verwendet werden, um Zeilen in der Zielzeile mit demselben Wert wie k zu aktualisieren. Durch die Verwendung von MAX() und GROUP BY wird in der Abfrage genau verdeutlicht, welcher Wert von v aus src verwendet wird:

MERGE INTO target USING (select k, max(v) as v from src group by k) AS b ON target.k = b.k
  WHEN MATCHED THEN UPDATE SET target.v = b.v
  WHEN NOT MATCHED THEN INSERT (k, v) VALUES (b.k, b.v);

Copy

Deterministische Ergebnisse für INSERT¶

Deterministische Zusammenführungen sind immer fehlerfrei.

Wenn MERGE eine WHEN NOT MATCHED ... THEN INSERT -Klausel enthält und das Ziel keine übereinstimmenden Zeilen enthält und die Quelle doppelte Werte enthält, erhält das Ziel eine Kopie der Zeile für die each -Kopie in der Quelle. (Ein Beispiel ist unten angegeben.)

Beispiele¶

Führen Sie eine einfache Zusammenführung (Merge) aus:

Erstellen und laden Sie die Tabellen:

CREATE TABLE target_table (ID INTEGER, description VARCHAR);

CREATE TABLE source_table (ID INTEGER, description VARCHAR);

Copy

INSERT INTO target_table (ID, description) VALUES
    (10, 'To be updated (this is the old value)')
    ;

INSERT INTO source_table (ID, description) VALUES
    (10, 'To be updated (this is the new value)')
    ;

Copy

Führen Sie die MERGE-Anweisung aus:

MERGE INTO target_table USING source_table 
    ON target_table.id = source_table.id
    WHEN MATCHED THEN 
        UPDATE SET target_table.description = source_table.description;
+------------------------+
| number of rows updated |
|------------------------|
|                      1 |
+------------------------+

Copy

Zeigen Sie die neuen Werte in der Zieltabelle an (die Quelltabelle bleibt unverändert):

SELECT * FROM target_table;
+----+---------------------------------------+
| ID | DESCRIPTION                           |
|----+---------------------------------------|
| 10 | To be updated (this is the new value) |
+----+---------------------------------------+
SELECT * FROM source_table;
+----+---------------------------------------+
| ID | DESCRIPTION                           |
|----+---------------------------------------|
| 10 | To be updated (this is the new value) |
+----+---------------------------------------+

Copy

Führen Sie eine grundlegende Zusammenführung mit einem Mix von Operationen durch (Löschen, Aktualisieren, Einfügen):

MERGE INTO t1 USING t2 ON t1.t1Key = t2.t2Key
    WHEN MATCHED AND t2.marked = 1 THEN DELETE
    WHEN MATCHED AND t2.isNewStatus = 1 THEN UPDATE SET val = t2.newVal, status = t2.newStatus
    WHEN MATCHED THEN UPDATE SET val = t2.newVal
    WHEN NOT MATCHED THEN INSERT (val, status) VALUES (t2.newVal, t2.newStatus);

Copy

Führen Sie eine Zusammenführung (Merge) durch, bei der die Quelle doppelte Werte und das Ziel keine übereinstimmenden Werte aufweist. Beachten Sie, dass alle Kopien des Quelldatensatzes in das Ziel eingefügt werden:

Schneiden Sie beide Tabellen ab, und laden Sie neue Zeilen in die Quelltabelle. Beachten Sie, dass die Zeilen Duplikate enthalten.

TRUNCATE TABLE source_table;

TRUNCATE TABLE target_table;

INSERT INTO source_table (ID, description) VALUES
    (50, 'This is a duplicate in the source and has no match in target'),
    (50, 'This is a duplicate in the source and has no match in target')
    ;

Copy

Führen Sie die MERGE-Anweisung aus:

MERGE INTO target_table USING source_table 
    ON target_table.id = source_table.id
    WHEN MATCHED THEN 
        UPDATE SET target_table.description = source_table.description
    WHEN NOT MATCHED THEN 
        INSERT (ID, description) VALUES (source_table.id, source_table.description);
+-------------------------+------------------------+
| number of rows inserted | number of rows updated |
|-------------------------+------------------------|
|                       2 |                      0 |
+-------------------------+------------------------+

Copy

Zeigen Sie den neuen Wert in der Zieltabelle an:

SELECT ID FROM target_table;
+----+
| ID |
|----|
| 50 |
| 50 |
+----+

Copy

Führen Sie Datensätze mit Verknüpfungen (Join) zusammen, die nicht deterministische und deterministische Ergebnisse erzeugen:

-- Setup for example.
CREATE TABLE target_orig (k NUMBER, v NUMBER);
INSERT INTO target_orig VALUES (0, 10);

CREATE TABLE src (k NUMBER, v NUMBER);
INSERT INTO src VALUES (0, 11), (0, 12), (0, 13);

-- Multiple updates conflict with each other.
-- If ERROR_ON_NONDETERMINISTIC_MERGE=true, returns an error;
-- otherwise updates target.v with a value (e.g. 11, 12, or 13) from one of the duplicate rows (row not defined).

CREATE OR REPLACE TABLE target CLONE target_orig;

MERGE INTO target
  USING src ON target.k = src.k
  WHEN MATCHED THEN UPDATE SET target.v = src.v;

-- Updates and deletes conflict with each other.
-- If ERROR_ON_NONDETERMINISTIC_MERGE=true, returns an error;
-- otherwise either deletes the row or updates target.v with a value (e.g. 12 or 13) from one of the duplicate rows (row not defined).

CREATE OR REPLACE TABLE target CLONE target_orig;

MERGE INTO target
  USING src ON target.k = src.k
  WHEN MATCHED AND src.v = 11 THEN DELETE
  WHEN MATCHED THEN UPDATE SET target.v = src.v;

-- Multiple deletes do not conflict with each other;
-- joined values that do not match any clause do not prevent the delete (src.v = 13).
-- Merge succeeds and the target row is deleted.

CREATE OR REPLACE TABLE target CLONE target_orig;

MERGE INTO target
  USING src ON target.k = src.k
  WHEN MATCHED AND src.v <= 12 THEN DELETE;

-- Joined values that do not match any clause do not prevent an update (src.v = 12, 13).
-- Merge succeeds and the target row is set to target.v = 11.

CREATE OR REPLACE TABLE target CLONE target_orig;

MERGE INTO target
  USING src ON target.k = src.k
  WHEN MATCHED AND src.v = 11 THEN UPDATE SET target.v = src.v;

-- Use GROUP BY in the source clause to ensure that each target row joins against one row
-- in the source:

CREATE OR REPLACE TABLE target CLONE target_orig;

MERGE INTO target USING (select k, max(v) as v from src group by k) AS b ON target.k = b.k
  WHEN MATCHED THEN UPDATE SET target.v = b.v
  WHEN NOT MATCHED THEN INSERT (k, v) VALUES (b.k, b.v);

Copy

Im folgenden Beispiel werden in der Tabelle members die Namen, Adressen und aktuellen Gebühren (members.fee) gespeichert, die an ein lokales Fitnessstudio gezahlt wurden. In der Tabelle signup wird das Anmeldedatum jedes Mitglieds gespeichert (signup.date). Durch die MERGE-Anweisung wird eine Gebühr in Höhe von 40 USD für Mitglieder erhoben, die vor mehr als 30 Tagen nach Ablauf der kostenlosen Testphase dem Fitnessstudio beigetreten sind:

MERGE INTO members m
  USING (
  SELECT id, date
  FROM signup
  WHERE DATEDIFF(day, CURRENT_DATE(), signup.date::DATE) < -30) s ON m.id = s.id
  WHEN MATCHED THEN UPDATE SET m.fee = 40;

Copy

MERGE¶

Syntax¶

Parameter¶

matchedClause (für Updates oder Löschungen)¶

notMatchedClause (für Eingaben)¶

Nutzungshinweise¶

Verhalten bei doppelter Verknüpfung (Join)¶

Deterministische Ergebnisse für UPDATE und DELETE¶

Deterministische Ergebnisse für UPDATE und DELETE¶

Deterministische Ergebnisse für INSERT¶

Beispiele¶

`matchedClause` (für Updates oder Löschungen)¶

`notMatchedClause` (für Eingaben)¶