MERGE
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
The MERGE statement allows data to be inserted or updated if the data matches a specified condition.
The MERGE operation updates a table using data from a source, which can be a table, view, or subselect. Rows in the target that match the source can be deleted or updated as specified. Rows that do not exist in the target can be inserted.
MERGE is a way of combining multiple operations rather than using multiple INSERT, UPDATE, and DELETE statements.
Updating the same row of the target table multiple times in the same MERGE statement is not allowed.
This statement has the following format:
MERGE INTO target-table [[AS] corr-name] USING table-ref ON join-condition clause-list
where clause-list is:
WHEN MATCHED [AND condition]
{UPDATE SET col = expr,... | DELETE}
WHEN NOT MATCHED [AND condition]
INSERT [(col-list)] VALUES (expr-list)
target-table
Specifies the name of the table into which the data is being updated or inserted. The target table must be a base table, not a view.
corr-name
Specifies a correlation name for the target table. It can be used in the join-condition, optional clause conditions, and any UPDATE clause SET value expressions.
table-ref
Specifies the source of the data to be updated or inserted. The source can be a table, view, or the result of a subselect.
join-condition
Specifies how candidate rows are formed from the target-table and the table-reference. The MERGE statement fragment:
INTO target-table USING table-reference ON join-condition
operates as if it were the following SELECT FROM-clause fragment:
FROM target-table RIGHT JOIN table-reference ON join-condition
The results of the (right outer) join are the candidate rows that are processed by the clause-list that follows.
The candidate row conceptually includes all columns of the table-reference plus all columns of the target-table. The target-table columns may be NULL if no target-table row joined that table-reference row; this is the usual rule for outer joins.
clause-list
Specifies an ordered list of WHEN-clauses that describe if and how each candidate row is applied to the target table. Clause-list can be a series of any of the following:
• WHEN MATCHED [AND condition] THEN DELETE
• WHEN MATCHED [AND condition] THEN UPDATE SET target-column = value,...
• WHEN NOT MATCHED [AND condition] THEN INSERT VALUES (value,...)
The values in an UPDATE SET clause can come from any tables listed including the target-table, including constants.
The values in an INSERT VALUES clause can come from the table-reference, including constants, but not the target-table.
A subquery is not allowed in a when-clause.
A WHEN MATCHED clause is executed if a target-table row joined to form the candidate row being processed.
A WHEN NOT MATCHED clause is executed if no target-table row joined while forming the candidate row.
WHEN MATCHED and WHEN NOT MATCHED clauses can be specified in any order, but the first clause applicable to a candidate row is the one executed by that row.
For any given result row, only the first applicable when-clause is run. If no clause matches, the candidate row is discarded, having no effect on the target table.
A mix of conditional and unconditional clauses can be used, but some combinations may not make sense. For example, it is legal to have more WHEN MATCHED clauses following an unconditional WHEN MATCHED clause, but those following clauses cannot have an effect, and so are ignored.
The join cannot produce more than one row per target row, or else an error is issued.