MERGE¶

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

WHEN MATCHED ... 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)

WHEN MATCHED ... THEN { UPDATE { ALL BY NAME | SET ... } | DELETE }

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

ALL BY NAME

Aktualisiert alle Spalten in der Zieltabelle mit Werten aus der Quelle. Jede Spalte in der Zieltabelle wird mit den Werten der gleichnamigen Spalte aus der Quelle aktualisiert.

Die Zieltabelle und die Quelle müssen die gleiche Anzahl von Spalten und die gleichen Namen für alle Spalten haben. Die Reihenfolge der Spalten kann jedoch zwischen Zieltabelle und Quelltabelle unterschiedlich sein.

SET col_name = expr [ , col_name = expr ... ]

Aktualisiert die angegebene Spalte in der Zieltabelle, indem der entsprechende Ausdruck für den neuen Spaltenwert verwendet wird (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 werden sollen.

DELETE

Löscht die Zeilen in der Zieltabelle, wenn sie mit der Quelle übereinstimmen.

notMatchedClause (für Eingaben)¶

WHEN NOT MATCHED ... 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)

WHEN NOT MATCHED ... THEN INSERT . { ALL BY NAME | [ ( col_name [ , ... ] ) ] VALUES ( expr [ , ... ] ) }

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

ALL BY NAME

Fügt alle Spalten in die Zieltabelle mit Werten aus der Quelle ein. Jede Spalte der Zieltabelle wird mit den Werten der Spalte mit demselben Namen aus der Quelle eingefügt.

Die Zieltabelle und die Quelle müssen die gleiche Anzahl von Spalten und die gleichen Namen für alle Spalten haben. Die Reihenfolge der Spalten kann jedoch zwischen Zieltabelle und Quelltabelle unterschiedlich sein.

( col_name [ , ... ] )

Gibt optional eine oder mehrere Spalten in der Zieltabelle an, die mit Werten aus der Quelle eingefügt werden sollen.

Standard: Kein Wert (alle Spalten in der Zieltabelle werden aktualisiert)

VALUES ( expr [ , ... ] )

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

Nicht 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 mindestens eine der folgenden Bedingungen erfüllt ist:

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

• 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.

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);

Deterministische Ergebnisse für INSERT¶

Deterministische Zusammenführungen sind immer fehlerfrei.

Wenn die MERGE-Anweisung 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 jede Kopie in der Quelle. Ein Beispiel dazu finden Sie unter Durchführen einer Zusammenführung mit Quellduplikaten.

Ausführen einer einfachen Zusammenführung, bei der die Werte aktualisiert werden¶

Im folgenden Beispiel wird eine einfache Zusammenführung durchgeführt, bei der die Werte in der Zieltabelle aktualisiert werden, indem Werte aus der Quelltabelle verwendet werden. Erstellen und laden Sie zwei Tabellen:

CREATE OR REPLACE TABLE merge_example_target (id INTEGER, description VARCHAR);

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

CREATE OR REPLACE TABLE merge_example_source (id INTEGER, description VARCHAR);

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

Zeigen Sie die Werte in den Tabellen an:

SELECT * FROM merge_example_target;

+----+---------------------------------------+
| ID | DESCRIPTION                           |
|----+---------------------------------------|
| 10 | To be updated (this is the old value) |
+----+---------------------------------------+

SELECT * FROM merge_example_source;

+----+---------------------------------------+
| ID | DESCRIPTION                           |
|----+---------------------------------------|
| 10 | To be updated (this is the new value) |
+----+---------------------------------------+

Führen Sie die MERGE-Anweisung aus:

MERGE INTO merge_example_target
  USING merge_example_source
  ON merge_example_target.id = merge_example_source.id
  WHEN MATCHED THEN
    UPDATE SET merge_example_target.description = merge_example_source.description;

+------------------------+
| number of rows updated |
|------------------------|
|                      1 |
+------------------------+

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

SELECT * FROM merge_example_target;

+----+---------------------------------------+
| ID | DESCRIPTION                           |
|----+---------------------------------------|
| 10 | To be updated (this is the new value) |
+----+---------------------------------------+

SELECT * FROM merge_example_source;

+----+---------------------------------------+
| ID | DESCRIPTION                           |
|----+---------------------------------------|
| 10 | To be updated (this is the new value) |
+----+---------------------------------------+

Einfache Zusammenführung mit mehreren Operationen durchführen¶

Führen Sie eine einfache Zusammenführung mit einem Mix von Operationen durch (INSERT, UPDATEund DELETE).

Erstellen und laden Sie zwei Tabellen:

CREATE OR REPLACE TABLE merge_example_mult_target (
  id INTEGER,
  val INTEGER,
  status VARCHAR);

INSERT INTO merge_example_mult_target (id, val, status) VALUES
  (1, 10, 'Production'),
  (2, 20, 'Alpha'),
  (3, 30, 'Production');

CREATE OR REPLACE TABLE merge_example_mult_source (
  id INTEGER,
  marked VARCHAR,
  isnewstatus INTEGER,
  newval INTEGER,
  newstatus VARCHAR);

INSERT INTO merge_example_mult_source (id, marked, isnewstatus, newval, newstatus) VALUES
  (1, 'Y', 0, 10, 'Production'),
  (2, 'N', 1, 50, 'Beta'),
  (3, 'N', 0, 60, 'Deprecated'),
  (4, 'N', 0, 40, 'Production');

Zeigen Sie die Werte in den Tabellen an:

SELECT * FROM merge_example_mult_target;

+----+-----+------------+
| ID | VAL | STATUS     |
|----+-----+------------|
|  1 |  10 | Production |
|  2 |  20 | Alpha      |
|  3 |  30 | Production |
+----+-----+------------+

SELECT * FROM merge_example_mult_source;

+----+--------+-------------+--------+------------+
| ID | MARKED | ISNEWSTATUS | NEWVAL | NEWSTATUS  |
|----+--------+-------------+--------+------------|
|  1 | Y      |           0 |     10 | Production |
|  2 | N      |           1 |     50 | Beta       |
|  3 | N      |           0 |     60 | Deprecated |
|  4 | N      |           0 |     40 | Production |
+----+--------+-------------+--------+------------+

Im folgenden Zusammenführungsbeispiel werden die folgenden Aktionen für die merge_example_mult_target-Tabelle ausgeführt:

• Löscht die Zeile mit auf 1 gesetzter id, weil die marked Spalte für die Zeile mit derselben id Y in merge_example_mult_source ist.

• Aktualisiert die Werte val und status in der Zeile mit auf 2 gesetzter id mit Werten in der Zeile mit der gleichen id in merge_example_mult_source, weil isnewstatus für dieselbe Zeile in merge_example_mult_source auf 1 gesetzt ist.

• Aktualisiert den val-Wert in der Zeile mit auf 3 gesetzter id mit dem Wert in der Zeile mit derselben id in merge_example_mult_source. Die MERGE-Anweisung aktualisiert nicht den status-Wert in merge_example_mult_target, weil isnewstatus für diese Zeile in merge_example_mult_source auf 0 gesetzt ist.

• Fügt die Zeile mit auf 4 gesetzter id ein, weil die Zeile in merge_example_mult_source existiert und es keine übereinstimmende Zeile in merge_example_mult_target gibt.

MERGE INTO merge_example_mult_target
  USING merge_example_mult_source
  ON merge_example_mult_target.id = merge_example_mult_source.id
  WHEN MATCHED AND merge_example_mult_source.marked = 'Y'
    THEN DELETE
  WHEN MATCHED AND merge_example_mult_source.isnewstatus = 1
    THEN UPDATE SET val = merge_example_mult_source.newval, status = merge_example_mult_source.newstatus
  WHEN MATCHED
    THEN UPDATE SET val = merge_example_mult_source.newval
  WHEN NOT MATCHED
    THEN INSERT (id, val, status) VALUES (
      merge_example_mult_source.id,
      merge_example_mult_source.newval,
      merge_example_mult_source.newstatus);

+-------------------------+------------------------+------------------------+
| number of rows inserted | number of rows updated | number of rows deleted |
|-------------------------+------------------------+------------------------|
|                       1 |                      2 |                      1 |
+-------------------------+------------------------+------------------------+

Um die Ergebnisse der Zusammenführung zu sehen, zeigen Sie die Werte in der merge_example_mult_target-Tabelle an:

SELECT * FROM merge_example_mult_target ORDER BY id;

+----+-----+------------+
| ID | VAL | STATUS     |
|----+-----+------------|
|  2 |  50 | Beta       |
|  3 |  60 | Production |
|  4 |  40 | Production |
+----+-----+------------+

Durchführen einer Zusammenführung mit ALL BY NAME¶

Im folgenden Beispiel wird eine Zusammenführung durchgeführt, bei der die Werte in der Zieltabelle eingefügt aktualisiert werden, indem Werte aus der Quelltabelle verwendet werden. In dem Beispiel werden die Unterklauseln WHEN MATCHED ... THEN ALL BY NAME und WHEN NOT MATCHED ... THEN ALL BY NAME verwendet, um anzugeben, dass die Zusammenführung für alle Spalten gelten soll.

Erstellen Sie zwei Tabellen mit der gleichen Anzahl von Spalten und den gleichen Namen für die Spalten, aber mit einer anderen Reihenfolge für zwei der Spalten:

CREATE OR REPLACE TABLE merge_example_target_all (
  id INTEGER,
  x INTEGER,
  y VARCHAR);

CREATE OR REPLACE TABLE merge_example_source_all (
  id INTEGER,
  y VARCHAR,
  x INTEGER);

Laden Sie die Tabellen:

INSERT INTO merge_example_target_all (id, x, y) VALUES
  (1, 10, 'Skiing'),
  (2, 20, 'Snowboarding');

INSERT INTO merge_example_source_all (id, y, x) VALUES
  (1, 'Skiing', 10),
  (2, 'Snowboarding', 25),
  (3, 'Skating', 30);

Zeigen Sie die Werte in den Tabellen an:

SELECT * FROM merge_example_target_all;

+----+----+--------------+
| ID |  X | Y            |
|----+----+--------------|
|  1 | 10 | Skiing       |
|  2 | 20 | Snowboarding |
+----+----+--------------+

SELECT * FROM merge_example_source_all;

+----+--------------+----+
| ID | Y            |  X |
|----+--------------+----|
|  1 | Skiing       | 10 |
|  2 | Snowboarding | 25 |
|  3 | Skating      | 30 |
+----+--------------+----+

Führen Sie die MERGE-Anweisung aus:

MERGE INTO merge_example_target_all
  USING merge_example_source_all
  ON merge_example_target_all.id = merge_example_source_all.id
  WHEN MATCHED THEN
    UPDATE ALL BY NAME
  WHEN NOT MATCHED THEN
    INSERT ALL BY NAME;

+-------------------------+------------------------+
| number of rows inserted | number of rows updated |
|-------------------------+------------------------|
|                       1 |                      2 |
+-------------------------+------------------------+

Zeigen Sie die neuen Werte in der Zieltabelle an:

SELECT *
  FROM merge_example_target_all
  ORDER BY id;

+----+----+--------------+
| ID |  X | Y            |
|----+----+--------------|
|  1 | 10 | Skiing       |
|  2 | 25 | Snowboarding |
|  3 | 30 | Skating      |
+----+----+--------------+

Durchführen einer Zusammenführung mit Quellduplikaten¶

Führen Sie eine Zusammenführung (Merge) durch, bei der die Quelle doppelte Werte und das Ziel keine übereinstimmenden Werte aufweist. Alle Kopien des Quelldatensatzes werden in das Ziel eingefügt. Weitere Informationen dazu finden Sie unter Deterministische Ergebnisse für INSERT.

Kürzen Sie beide Tabellen und laden Sie neue Zeilen in die Quelltabelle, die Duplikate enthält:

TRUNCATE table merge_example_target;

TRUNCATE table merge_example_source;

INSERT INTO merge_example_source (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');

Das merge_example_target hat keine Werte. Zeigen Sie die Werte in der merge_example_source-Tabelle an:

SELECT * FROM merge_example_source;

+----+--------------------------------------------------------------+
| ID | DESCRIPTION                                                  |
|----+--------------------------------------------------------------|
| 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 |
+----+--------------------------------------------------------------+

Führen Sie die MERGE-Anweisung aus:

MERGE INTO merge_example_target
  USING merge_example_source
  ON merge_example_target.id = merge_example_source.id
  WHEN MATCHED THEN
    UPDATE SET merge_example_target.description = merge_example_source.description
  WHEN NOT MATCHED THEN
    INSERT (id, description) VALUES
      (merge_example_source.id, merge_example_source.description);

+-------------------------+------------------------+
| number of rows inserted | number of rows updated |
|-------------------------+------------------------|
|                       2 |                      0 |
+-------------------------+------------------------+

Zeigen Sie die neuen Werte in der Zieltabelle an:

SELECT * FROM merge_example_target;

+----+--------------------------------------------------------------+
| ID | DESCRIPTION                                                  |
|----+--------------------------------------------------------------|
| 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 |
+----+--------------------------------------------------------------+

Führen Sie eine Zusammenführung mit deterministischen und nicht deterministischen Ergebnissen durch¶

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

Erstellen und laden Sie zwei Tabellen:

CREATE OR REPLACE TABLE merge_example_target_orig (k NUMBER, v NUMBER);

INSERT INTO merge_example_target_orig VALUES (0, 10);

CREATE OR REPLACE TABLE merge_example_src (k NUMBER, v NUMBER);

INSERT INTO merge_example_src VALUES (0, 11), (0, 12), (0, 13);

Wenn Sie die Zusammenführung im folgenden Beispiel ausführen, stehen mehrere Aktualisierungen miteinander in Konflikt. Wenn der ERROR_ON_NONDETERMINISTIC_MERGE-Sitzungsparameter auf true gesetzt ist, gibt die MERGE-Anweisung einen Fehler zurück. Andernfalls aktualisiert die MERGE-Anweisung merge_example_target_clone.v mit einem Wert (z. B. 11, 12``oder ``13) aus einer der doppelten Zeilen (Zeile nicht definiert):

CREATE OR REPLACE TABLE merge_example_target_clone
  CLONE merge_example_target_orig;

MERGE INTO  merge_example_target_clone
  USING merge_example_src
  ON merge_example_target_clone.k = merge_example_src.k
  WHEN MATCHED THEN UPDATE SET merge_example_target_clone.v = merge_example_src.v;

Aktualisierungen und Löschungen steht miteinander in Konflikt. Wenn der ERROR_ON_NONDETERMINISTIC_MERGE-Sitzungsparameter auf true gesetzt ist, gibt die MERGE-Anweisung einen Fehler zurück. Andernfalls löscht die MERGE-Anweisung entweder die Zeile oder aktualisiert merge_example_target_clone.v mit einem Wert (z. B. 12 oder 13) aus einer der doppelten Zeilen (Zeile nicht definiert):

CREATE OR REPLACE TABLE merge_example_target_clone
  CLONE merge_example_target_orig;

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

Mehrere Löschvorgänge stehen nicht in Konflikt miteinander. Zusammengeführte Werte, die keiner Klausel entsprechen, verhindern das Löschen nicht (merge_example_src.v = 13). Die MERGE-Anweisung ist erfolgreich und die Zielzeile wird gelöscht:

CREATE OR REPLACE TABLE target CLONE merge_example_target_orig;

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

Verknüpfte Werte, die keiner Klausel entsprechen, verhindern nicht eine Aktualisierung (merge_example_src.v = 12, 13). Die MERGE-Anweisung ist erfolgreich und die Zielzeile wird auf target.v = 11 gesetzt:

CREATE OR REPLACE TABLE merge_example_target_clone CLONE target_orig;

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

Verwenden Sie GROUP BY in der Quellklausel, um sicherzustellen, dass jede Zielzeile mit genau einer Zeile in der Quelle verknüpft wird:

CREATE OR REPLACE TABLE merge_example_target_clone CLONE merge_example_target_orig;

MERGE INTO merge_example_target_clone
  USING (SELECT k, MAX(v) AS v FROM merge_example_src GROUP BY k) AS b
  ON merge_example_target_clone.k = b.k
  WHEN MATCHED THEN UPDATE SET merge_example_target_clone.v = b.v
  WHEN NOT MATCHED THEN INSERT (k, v) VALUES (b.k, b.v);

Führen Sie eine Zusammenführung auf Basis von DATE-Werten durch¶

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;