MERGE

Purpose

Use this statement to merge rows from a source into a target table. The source can be a table or a query.

The rows returned by the source determine which rows in the target table will be updated, deleted, or inserted. Therefore, the MERGE statement unites these three statements - UPDATE, DELETE, and INSERT.

The source can contain data for new customers, customers to be dropped, or changes to existing customers. It can be a table or a query. The MERGE statement can use this data to insert new customers into the target table, delete non-valid customers, and update existing customers.

Prerequisites

• You need either the system privilege USE ANY SCHEMA or the object privilege USAGE on the target schema, or the schema must be owned by you or one of your assigned roles.

• You need to have the appropriate INSERT, DELETE, and UPDATE privileges on the target table.
• You need to have the appropriate SELECT privileges on the update table.

Usage notes

• The ON condition describes the correlation between the two tables (similar to a join). The MATCHED clause is used for matching row pairs, and the NOT MATCHED clause is used for those that do not match. In the ON condition, only equivalence conditions (=) are permitted.
• In the UPDATE clause, the optional WHERE condition specifies the circumstances under which the UPDATE is conducted, whereby it is permissible for both the target table and the update table to be referenced for this. With the support of the optional DELETE condition, it is possible to delete rows in the target table. Only the rows that have been matched by the WHERE clause of the UPDATE are taken into consideration for the DELETE clause. The WHERE clause of the DELETE statement uses the values after the UPDATE.
• In the DELETE clause, the optional WHERE condition specifies the circumstances under which the DELETE is performed.
• In the INSERT clause, the optional WHERE condition specifies the circumstances under which the INSERT is performed. It is permissible to reference columns of the update table in the INSERT clause.
• The UPDATE, DELETE, and INSERT clauses are optional with the restriction that at least one must be specified. The order of these clauses is interchangeable.
• The INSERT and UPDATE clauses treat the default values and identity columns in the same way as in the INSERT and UPDATE statements. However, the only exception is that INSERT DEFAULT VALUES is not allowed.
• If there are several entries in the update table that could apply to an UPDATE of a single row in the target table, then this displays the error message "Unable to get a stable set of rows in the source tables", if the UPDATE candidates would change the original value of the target table.
• If several entries in the update table could apply to a DELETE of a single row in the target table, this displays the error message "Unable to get a stable set of rows in the source tables".

Examples

Consider the following sample tables and examples statements based on these tables:

Staff

Changes

Deletes

-- Merging the table updates
MERGE INTO staff T
      USING changes U
      ON T.name = U.name
      WHEN MATCHED THEN UPDATE SET T.salary = U.salary,
                                   T.lastChange = CURRENT_DATE
                        WHERE T.salary < U.salary
      WHEN NOT MATCHED THEN INSERT VALUES (U.name,U.salary,CURRENT_DATE);

SELECT * FROM staff;

Results

-- Merging the table deletes
MERGE INTO staff T
      USING deletes U
      ON T.name = U.name
      WHEN MATCHED THEN DELETE;

SELECT * FROM staff;

Results

Upserting a single row

You can also use MERGE to insert a row if it does not exist, or update it if it already exists. This is often called an upsert. The USING clause can contain a query that supplies the values for one row; a separate source table is not required.

The following example assumes that the customer table has the columns customer_id, name, and email:

MERGE INTO customer AS target
USING (
    SELECT
        42 AS customer_id,
        'John Doe' AS name,
        'johndoe@example.com' AS email
) AS src
ON target.customer_id = src.customer_id
WHEN MATCHED THEN
    UPDATE SET
        target.name = src.name,
        target.email = src.email
WHEN NOT MATCHED THEN
    INSERT (customer_id, name, email)
    VALUES (src.customer_id, src.name, src.email);

The ON condition checks whether a row with customer_id 42 exists in customer. If it exists, MERGE updates its name and email. If it does not exist, MERGE inserts a new row.