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 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.

Syntax

merge::=

Merge Statement

Merge Statement

Merge Statement

Merge Statement

merge_update_clause::=

Merge Update Clause

Merge Update Clause

merge_delete_clause::=

Merge Delete Clause

merge_insert_clause::=

Merge Insert Clause

Merge Insert Clause

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".

Example

/* Sample tables
staff:                                 changes:            deletes:
name    | salary | lastChange         name | salary        name
-----------------|-----------         ----------------     ---------
meier   | 30000  | 2006-01-01         schmidt | 43000      meier
schmidt | 40000  | 2006-05-01         hofmann | 35000
mueller | 50000  | 2005-08-01         meier   | 29000
*/


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

NAME                            SALARY               LASTCHANGE
------------------------------  -------------------  ----------
meier                                         30000  2006-01-01
schmidt                                       43000  2010-10-06
mueller                                       50000  2005-08-01
hofmann                                       35000  2010-10-06


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

SELECT * FROM staff;

NAME                            SALARY               LASTCHANGE
------------------------------  -------------------  ----------
hofmann                                       35000  2010-10-06
schmidt                                       43000  2010-10-06
mueller                                       50000  2005-08-01