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.

MERGEchanges data in the target_table based on the contents of the source_table. The search_condition defines a condition, such as a relation from identical columns, to associate the source and target data.

MERGE supports an arbitrary number of WHEN clauses. MATCHED conditions can execute DELETE or UPDATE operations on the target data, while NOT MATCHED conditions can add data from the source to the target table with INSERT. Additional conditions can narrow down the affected rows.

For each source row, the WHEN clauses are processed in order. Only the first matching WHEN clause is executed and subsequent clauses are ignored. The query fails if a single target table row matches more than one source row.

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 on any field of the source.

Typical usage of MERGE involves two tables with similar structure, containing different data. For example, the source table is part of a transactional usage in a production system, while the target table is located in a data warehouse used for analytics. Periodically, MERGE operations are run to combine recent production data with long-term data in the analytics warehouse. As long as you can define a search condition between the two tables, you can also use very different tables.

Examples

Delete all customers mentioned in the source table:

MERGE INTO accounts t USING monthly_accounts_update 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 accounts t USING monthly_accounts_update 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 accounts t USING monthly_accounts_update 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

Any connector can be used as a source table for a MERGE statement. Only connectors which support the MERGE statement can be the target of a merge operation. See the connector documentation for more information.