MERGE

Purpose

Using the MERGE statement, it is possible to merge the contents of an update table into a target table. The rows of the update table determines which rows will be changed, deleted, or inserted. Therefore, the MERGE statement unites these three statements - UPDATE, DELETE, and INSERT.

For example, the update table can contain the data of new customers or customers to be dropped or the change information of already existing customers. The MERGE statement can now be used to insert the new customers into the target table, delete non-valid customers, and update the changes of 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