Categories:
DML Commands - General

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

In this Topic:

Syntax

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

Where:

matchedClause ::=
  WHEN MATCHED [ AND <case_predicate> ] THEN { UPDATE SET { <col_name> = <expr> } | DELETE } [ ... ]
notMatchedClause ::=
   WHEN NOT MATCHED [ AND <case_predicate> ] THEN INSERT [ ( <col_name> [ , ... ] ) ] VALUES ( <expr> [ , ... ] )

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

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

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

Errors can be avoided by using GROUP BY or ORDER BY in the source clause to ensure that each target row joins against one row (at most) in the source.

Examples

Perform a basic merge:

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

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

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;