MERGE Semantics

The MERGE statement is a DML statement that merges matching rows from a source query or table to a target table. If a row in the source query or table does not exist in the target table, then MERGE inserts the row into the target table.
A MERGE statement contains:

  • A source query or table. If there is a source query, it is also referred to as the USING clause.

  • A target table.

  • An ON predicate that evaluates the rows coming from the source.

  • An UPDATE or an INSERT clause, or both. If the ON predicate evaluates to TRUE for a given row from the source, then the UPDATE clause is executed for that row. Otherwise the INSERT clause is executed (the row is inserted in the target table).

  • In the MERGE syntax, the INSERT clause is specified as WHEN NOT MATCHED THEN INSERT. The UPDATE clause is specified as WHEN MATCHED THEN UPDATE.

  • The UPDATE clause may refer to one or more columns. Those columns must not be part of the clustering key.

If there is an INSERT clause in the MERGE statement, then the ON predicate must select at the most one row (its search condition must be a predicate on all the columns that are in the clustering key of the target table). If there is only an UPDATE clause (with no INSERT), then the ON predicate can select more than one row (the search condition can be on nonkey columns).

The semantic restrictions are:

  • A target table cannot be a view, a materialized view, or a sequence generator.

  • Sequence generator cannot be used in any clauses.

  • MERGE is not allowed if the target table has triggers, materialized views, or referential integrity constraints.

  • MERGE and SELECT statement in the USING clause cannot have SET ON ROLLBACK, embedded update or delete, and stream access.

  • Key values specified in the ON clause must match key values specified in the INSERT clause.

  • The ON clause, WHEN MATCHED clause, and WHEN NOT MATCHED clause cannot contain a subquery.

  • Update of clustering key columns is not supported.