MERGE — conditionally insert, update, or delete rows of a table.
MERGE allows you to select rows from one or more sources for update or insertion into a table. You specify the join condition to determine whether to update or insert into the target table. You specify conditional UPDATE and INSERT statements using the WHERE clause in the MERGE statement.
MERGE provides a single SQL statement that can conditionally INSERT, UPDATE, or DELETE rows, a task which would otherwise require multiple procedural language statements.
For more information, see MERGE statement.
The name (optionally schema-qualified) of the target table to merge into.
A substitute name for the target table. When an alias is provided, it completely hides the actual name of the table.
The name (optionally schema-qualified) of the source table or a source query. The source table is the source table name, view name, or transition table name. The source query (SELECT or VALUES statement) supplies the rows to be merged into the target_table_name.
A substitute name for the data source. When an alias is provided, it completely hides the actual name of the table.
An expression resulting in a value of type boolean similar to a WHERE clause that specifies which rows in the data_source match rows in the target_table_name.
Specifies the new column values of the target table. It performs this update if the join_condition of the ON clause is true.
Specify the where_condition if you want the database to execute the update operation only if the specified condition is true. The condition can refer to either the data source or the target table. If the condition is not true, then the database skips the update operation when merging the row into the table.
Specify the DELETE where_condition to clean up data in a table while populating or updating it. The only rows affected by this clause are those rows in the target table that are updated by the merge operation. The DELETE WHERE condition evaluates the updated value, not the original value that was evaluated by the UPDATE SET ... WHERE condition. If a row of the target table meets the DELETE condition but is not included in the join defined by the ON clause, then it is not deleted.
You can specify this clause by itself or with the merge_insert_clause. If you specify both, then they can be in either order.
The merge_insert_clause specifies values to insert into the column of the target table if the condition of the ON clause is false. If you omit the column list after the INSERT keyword, then the number of columns in the target table must match the number of values in the VALUES clause.
To insert all of the source rows into the table, you can use a constant filter predicate in the ON clause condition. An example of a constant filter predicate is
ON (0=1). It recognizes such a predicate and makes an unconditional insert of all source rows into the table. This approach is different from omitting the merge_update_clause. In that case, the database still must perform a join. With constant filter predicate, no join is performed.
Specify the where_clause if you want the database to execute the insert operation only if the specified condition is true. The condition can refer only to the data source table. Database skips the insert operation for all rows for which the condition is not true.
You can specify this clause by itself or with the merge_update_clause. If you specify both, then they can be in either order.
Add rows to both the tables:
This example shows how to UPDATE and INSERT rows on the target table using the MERGE statement:
This example shows how to UPDATE, INSERT, and conditionally DELETE rows on the target table using MERGE statement:
This example shows how to UPDATE, INSERT, and DELETE rows on the target table conditionally using the MERGE statement: