MERGE¶

Inserts, updates, and deletes values in a table that are based on values in a second table or a subquery. Merging can be useful if the second table is a change log that contains new rows (to be inserted), modified rows (to be updated), or marked rows (to be deleted) in the target table.

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

Übereinstimmende Werte (für Aktualisierungen und Löschungen).
Values that don’t match (for inserts).

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 { ALL BY NAME | SET <col_name> = <expr> [ , <col_name> = <expr> ... ] } | DELETE } [ ... ]

Copy

notMatchedClause ::=
   WHEN NOT MATCHED
     [ AND <case_predicate> ]
     THEN INSERT { ALL BY NAME | [ ( <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 ... 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 ... ]

Updates the specified column in the target table by using the corresponding expression for the new column value (can refer to both the target and source relations).

In a single SET subclause, you can specify multiple columns to update.

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 [ , ... ] ) }

Specifies the action to perform when the values don’t match.

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.

( col_name [ , ... ] )

Optionally specifies one or more columns in the target table to be inserted with values from the source.

Default: No value (all columns in the target table are inserted)

VALUES ( expr [ , ... ] )

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

Nutzungshinweise¶

A single MERGE statement can include multiple matching and not-matching clauses (that is, WHEN MATCHED ... and WHEN NOT MATCHED ...).
Any matching or not-matching clause that omits the AND subclause (default behavior) must be the last of its clause type in the statement (for example, a WHEN MATCHED ... clause can’t be followed by a WHEN MATCHED AND ... clause). Doing so results in an unreachable case, which returns an error.

Verhalten bei doppelter Verknüpfung (Join)¶

Wenn mehrere Zeilen in der Quelltabelle mit einer einzelnen Zeile in der Zieltabelle übereinstimmen, können die Ergebnisse deterministisch oder nicht deterministisch sein. Dieser Abschnitt beschreibt MERGE-Verhalten für diese Anwendungsfälle.

Nicht deterministische Ergebnisse für UPDATE und DELETE¶

When a merge joins a row in the target table against multiple rows in the source, the following join conditions produce nondeterministic results (that is, the system is unable to determine the source value to use to update or delete the target row):

A target row is selected to be updated with multiple values (for example, WHEN MATCHED ... THEN UPDATE).
A target row is selected to be both updated and deleted (for example, 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¶

Deterministic merges always complete without error. A merge is deterministic if it meets at least one of the following conditions for each target row:

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.

Bemerkung

To avoid errors when multiple rows in the data source (that is, the source table or subquery) match the target table based on the ON condition, use GROUP BY in the source clause to ensure that each target row joins against one row (at most) in the source.

In the following example, assume src includes multiple rows with the same k value. It’s ambiguous which values (v) will be used to update rows in the target row with the same value of k. By using the MAX function and GROUP BY, the query clarifies exactly which value of v from src is used:

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.

If the MERGE statement contains a WHEN NOT MATCHED ... THEN INSERT clause, and if there are no matching rows in the target, and if the source contains duplicate values, then the target gets one copy of the row for each copy in the source. For an example, see Durchführen einer Zusammenführung mit Quellduplikaten.

Beispiele¶

Die folgenden Beispiele verwenden den MERGE-Befehl:

Perform a basic merge that updates values
Perform a basic merge with multiple operations
Durchführen einer Zusammenführung mit ALL BY NAME
Durchführen einer Zusammenführung mit Quellduplikaten
Perform a merge with deterministic and nondeterministic results
Führen Sie eine Zusammenführung auf Basis von DATE-Werten durch

Perform a basic merge that updates values¶

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

Copy

Display the values in the tables:

SELECT * FROM merge_example_target;

Copy

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

SELECT * FROM merge_example_source;

Copy

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

Run the MERGE statement:

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;

Copy

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

Display the new values in the target table (the source table is unchanged):

SELECT * FROM merge_example_target;

Copy

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

SELECT * FROM merge_example_source;

Copy

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

Perform a basic merge with multiple operations¶

Perform a basic merge with a mix of operations (INSERT, UPDATE, and DELETE).

Create and load two tables:

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

Copy

Display the values in the tables:

SELECT * FROM merge_example_mult_target;

Copy

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

SELECT * FROM merge_example_mult_source;

Copy

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

Copy

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

Copy

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

Copy

Load the tables:

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

Copy

Display the values in the tables:

SELECT * FROM merge_example_target_all;

Copy

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

SELECT * FROM merge_example_source_all;

Copy

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

Run the MERGE statement:

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;

Copy

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

Display the new values in the target table:

SELECT *
  FROM merge_example_target_all
  ORDER BY id;

Copy

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

Durchführen einer Zusammenführung mit Quellduplikaten¶

Perform a merge in which the source has duplicate values and the target has no matching values. All copies of the source record are inserted into the target. For more information, see Deterministische Ergebnisse für INSERT.

Truncate both tables and load new rows into the source table that include duplicates:

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

Copy

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

SELECT * FROM merge_example_source;

Copy

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

Run the MERGE statement:

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

Copy

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

Display the new values in the target table:

SELECT * FROM merge_example_target;

Copy

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

Perform a merge with deterministic and nondeterministic results¶

Merge records by using joins that produce nondeterministic and deterministic results.

Create and load two tables:

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

Copy

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;

Copy

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;

Copy

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;

Copy

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;

Copy

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

Copy

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;

Copy

MERGE¶

Syntax¶

Parameter¶

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

notMatchedClause (für Eingaben)¶

Nutzungshinweise¶

Verhalten bei doppelter Verknüpfung (Join)¶

Nicht deterministische Ergebnisse für UPDATE und DELETE¶

Deterministische Ergebnisse für UPDATE und DELETE¶

Deterministische Ergebnisse für INSERT¶

Beispiele¶

Perform a basic merge that updates values¶

Perform a basic merge with multiple operations¶

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

Durchführen einer Zusammenführung mit Quellduplikaten¶

Perform a merge with deterministic and nondeterministic results¶

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

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

`notMatchedClause` (für Eingaben)¶