MERGE#

Synopsis#

MERGE INTO target_table [ [ AS ]  target_alias ]
USING { source_table | query } [ [ AS ] source_alias ]
ON search_condition
when_clause [...]

where when_clause is one of

WHEN MATCHED [ AND condition ]
    THEN DELETE
WHEN MATCHED [ AND condition ]
    THEN UPDATE SET ( column = expression [, ...] )
WHEN NOT MATCHED [ AND condition ]
    THEN INSERT [ column_list ] VALUES (expression, ...)

Description#

Conditionally update and/or delete rows of a table and/or insert new rows into a table.

MERGE supports an arbitrary number of WHEN clauses with different MATCHED conditions, executing the DELETE, UPDATE or INSERT operation in the first WHEN clause selected by the MATCHED state and the match condition.

In WHEN clauses with UPDATE operations, the column value expressions can depend on any field of the target or the source. In the NOT MATCHED case, the INSERT expressions can depend only on the source.

Each row in the source must match at most one row in the target table. If more than one target table row is matched by a source row, a CONSTRAINT_VIOLATION exception is raised.

Examples#

Delete all customers mentioned in the source table:

MERGE INTO target_table t USING source_table s ON (t.customer = s.customer)
    WHEN MATCHED
        THEN DELETE

For matching customer rows, increment the purchases, and if there is no match, insert the row from the source table:

MERGE INTO target_table t USING source_table s ON (t.customer = s.customer)
    WHEN MATCHED
        THEN UPDATE SET purchases = s.purchases + t.purchases
    WHEN NOT MATCHED
        THEN INSERT (customer, purchases, address)
              VALUES(s.customer, s.purchases, s.address)

MERGE into the target table from the source table, deleting any matching target row for which the source address is Centreville. For all other matching rows, add the source purchases and set the address to the source address, if there is no match in the target table, insert the source table row:

MERGE INTO target_table t USING source_table s ON (t.customer = s.customer)
    WHEN MATCHED AND s.address = 'Centreville'
        THEN DELETE
    WHEN MATCHED
        THEN UPDATE SET purchases = s.purchases + t.purchases, address = s.address
    WHEN NOT MATCHED
        THEN INSERT (customer, purchases, address)
              VALUES(s.customer, s.purchases, s.address)

Limitations#

Some connectors have limited or no support for MERGE. See connector documentation for more details.