MERGE¶

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

The command supports semantics for handling the following cases:

Values that match (for updates and deletes).
Values that do not match (for inserts).

See also:: DELETE , UPDATE

Syntax¶

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

Copy

Where:

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

Parameters¶

target_table: Specifies the table to merge.
source: Specifies the table or subquery to join with the target table.
join_expr: Specifies the expression on which to join the target table and source.

`matchedClause` (for updates or deletes)¶

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

Specifies the action to perform when the values match.

AND case_predicate

Optionally specifies an expression which, when true, causes the matching case to be executed.

Default: No value (matching case is always executed)

SET col_name = expr [ … ]

Specifies the column within the target table to be updated or inserted and 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.

`notMatchedClause` (for inserts)¶

WHEN NOT MATCHED ... THEN INSERT

Specifies the action to perform when the values do not match.

AND case_predicate

Optionally specifies an expression which, when true, causes the not-matching case to be executed.

Default: No value (not-matching case is always executed)

( col_name [ , ... ] )

Optionally specifies one or more columns within the target table to be updated or inserted.

Default: No value (all columns within the target table are updated or inserted)

VALUES ( expr [ , ... ] )

Specifies the corresponding expressions for the inserted column values (must refer to the source relations).

Usage notes¶

A single MERGE statement can include multiple matching and not-matching clauses (i.e. 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 (e.g. a WHEN MATCHED ... clause cannot be followed by a WHEN MATCHED AND ... clause). Doing so results in an unreachable case, which returns an error.

Duplicate join behavior¶

Nondeterministic results for UPDATE and DELETE¶

When a merge joins a row in the target table against multiple rows in the source, the following join conditions produce nondeterministic results (i.e. 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 (e.g. WHEN MATCHED ... THEN UPDATE).
A target row is selected to be both updated and deleted (e.g. WHEN MATCHED ... THEN UPDATE , WHEN MATCHED ... THEN DELETE).

In this situation, the outcome of the merge depends on the value specified for the ERROR_ON_NONDETERMINISTIC_MERGE session parameter:

If TRUE (default value), the merge returns an error.
If FALSE, one row from among the duplicates is selected to perform the update or delete; the row selected is not defined.

Deterministic results for UPDATE and DELETE¶

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

One or more source rows satisfy the WHEN MATCHED ... THEN DELETE clauses, and no other source rows satisfy any WHEN MATCHED clauses

OR
Exactly one source row satisfies a WHEN MATCHED ... THEN UPDATE clause, and no other source rows satisfy any WHEN MATCHED clauses.

This makes MERGE semantically equivalent to the UPDATE and DELETE commands.

Note

To avoid errors when multiple rows in the data source (i.e. 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 MAX() 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

Deterministic results for INSERT¶

Deterministic merges always complete without error.

If the MERGE 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. (An example is included below.)

Examples¶

Perform a simple merge:

Create and load the tables:

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

Execute the MERGE statement:

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

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

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

Perform a basic merge with a mix of operations (delete, update, insert):

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

Perform a merge in which the source has duplicate values and the target has no matching values. Note that all copies of the source record are inserted into the target:

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

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

Execute the MERGE statement:

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

Display the new value in the target table:

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

Copy

Merge records using joins that produce nondeterministic and deterministic results:

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

In the following example, the members table stores the names, addresses, and current fees (members.fee) paid to a local gym. The signup table stores each member’s signup date (signup.date). The MERGE statement applies a standard $40 fee to members who joined the gym more than 30 days ago, after the free trial expired:

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¶

Parameters¶

matchedClause (for updates or deletes)¶

notMatchedClause (for inserts)¶

Usage notes¶

Duplicate join behavior¶

Nondeterministic results for UPDATE and DELETE¶

Deterministic results for UPDATE and DELETE¶

Deterministic results for INSERT¶

Examples¶

`matchedClause` (for updates or deletes)¶

`notMatchedClause` (for inserts)¶