MERGE statement The MERGE statement scans a table and either INSERTs, UPDATEs, or DELETEs rows depending on whether the rows satisfy a specified condition. MERGE statement SQL statementsMERGE Syntax MERGE INTO targetTable [ [ AS ] targetCorrelationName ] USING sourceTable [ [ AS ] sourceCorrelationName ] ON searchCondition mergeWhenClause [ mergeWhenClause ]*

Both targetTable and sourceTable are s.

targetTable must identify a base table. targetTable may not be a transition table in a triggered statement, and it may not be a synonym.

sourceTable must identify a base table or a table function, and it may not be a synonym.

Both targetCorrelationName and sourceCorrelationName are s.

The unqualified source table name (or its correlation name) may not be the same as the unqualified target table name (or its correlation name).

The searchCondition is a Boolean expression. Columns referenced by the searchCondition must be in either targetTable or sourceTable. Functions mentioned in the searchCondition may not modify SQL data.

The row count for a successful MERGE statement is the total number of rows inserted, updated, and deleted by the statement.

The MERGE statement is valid only after a database has been fully upgraded to Release 10.11 or higher. (See "Upgrading a database" in the for more information.) This statement has no meaning in a database that is at Release 10.10 or lower. mergeWhenClause mergeWhenMatched | mergeWhenNotMatched mergeWhenMatched WHEN MATCHED [ AND matchRefinement ] THEN { mergeUpdate | DELETE }

The matchRefinement is a Boolean expression. Columns referenced by the matchRefinement must be in either targetTable or sourceTable. Functions mentioned in the matchRefinement may not modify SQL data.

 mergeWhenNotMatched WHEN NOT MATCHED [ AND matchRefinement ] THEN mergeInsert

The matchRefinement is a Boolean expression. Columns referenced by the matchRefinement must be in either targetTable or sourceTable. Functions mentioned in the matchRefinement may not modify SQL data.

Although permitted to do so by the SQL Standard, does not currently support subqueries in WHEN [ NOT ] MATCHED clauses.

 mergeUpdate UPDATE SET column-Name = value [, column-Name = value ]*

Columns updated must be columns in targetTable.

Functions mentioned in the UPDATE values may not modify SQL data.

On the right side of SET operators for UPDATE actions, DEFAULT is the only value allowed for generated and identity columns.

No list of updated columns may mention the same column more than once.

The data types of updated values must be assignable to the corresponding columns according to the rules documented in .

 mergeInsert INSERT [ ( Simple-column-Name [ , Simple-column-Name ]* ) ] VALUES ( value [, value ]* )

Columns inserted must be columns in targetTable.

Functions mentioned in the INSERT values may not modify SQL data.

No list of inserted columns may mention identity columns, or may mention the same column more than once.

In a VALUES clause, DEFAULT is the only allowed value for generated columns.

The data types of inserted values must be assignable to the corresponding columns according to the rules documented in .
Required privileges

The user who executes a MERGE statement must have the following privileges. See for information on privileges.

  • UPDATE privilege on every updated column of targetTable. A blanket UPDATE privilege on the entire targetTable would cover this.
  • INSERT privilege on targetTable if there are WHEN NOT MATCHED clauses.
  • DELETE privilege on targetTable if there are WHEN MATCHED ... THEN DELETE clauses.
  • EXECUTE privilege on all functions mentioned in the Boolean expressions and in the INSERT/UPDATE values.
  • USAGE privilege on all sequences and user-defined types mentioned in the Boolean expressions and in the INSERT/UPDATE values. See and for more information.
  • SELECT privilege on all columns mentioned in the Boolean expressions and the value expressions of SET clauses.
MERGE statement behavior

The MERGE statement behaves as described in the following table.

Merge statement behaviorThis table lists and describes some specific behaviors of the MERGE statement. Situation or Behavior Description Source table is empty If the sourceTable is empty, a "no data" warning is raised with SQLState 02000. An initial join is performed Before any changes are made to targetTable, the sourceTable is joined to the targetTable by means of the ON clause. Call this join result J. Let N denote the rows in sourceTable missing from this join. Clause order is important The mergeWhenMatched and mergeWhenNotMatched clauses are applied in declaration order. The first matched clause wins For each row in J, applies only the first mergeWhenMatched clause whose matchRefinement is satisfied. The first not matched clause wins For each row in N, applies only the first mergeWhenNotMatched clause whose matchRefinement is satisfied. Double dipping is not permitted A cardinality violation is raised if a MERGE statement attempts to change (update or delete) the same row twice. This condition can occur if more than one source row joins to the same target row.
Examples MERGE INTO hotIssues h USING issues i ON h.issueID = i.issueID WHEN MATCHED AND i.lastUpdated = CURRENT_DATE THEN UPDATE SET h.lastUpdated = i.lastUpdated WHEN MATCHED AND i.lastUpdated < CURRENT_DATE THEN DELETE WHEN NOT MATCHED AND i.lastUpdated = CURRENT_DATE THEN INSERT VALUES ( i.issueID, i.lastUpdated ); MERGE INTO companies c USING adhocInvoices a ON a.companyName = c.companyName WHEN NOT MATCHED THEN INSERT ( companyName ) VALUES ( a.companyName ); MERGE INTO warehouse.productList w USING production.productList p ON w.productID = p.productID WHEN MATCHED and w.lastUpdated != p.lastUpdated THEN UPDATE SET lastUpdated = p.lastUpdated, description = p.description, price = p.price WHEN NOT MATCHED THEN INSERT values ( p.productID, p.lastUpdated, p.description, p.price );