MERGE Statement

MERGE is an ANSI 2003 standard statement. Given a set of source records and a target table, the MERGE statement provides an effective way to merge the source records with the data in the target table. It updates the rows in the target table that match the rows in the source, and inserts the rows that do not match in the target table. The source records can come from a query.

Users must have the INSERT and UPDATE object privileges on the target table and the READ or SELECT object privilege on the source tables. DELETE clause is not supported in SQL/MX Release 3.5. The target cannot be:

  • Tables with triggers, RI constraints, Materialized Views, user-defined identity columns, or sequence generator objects.

  • Derived tables such as views and sub queries.

  • SQL/MP tables and aliases.