The MERGE statement (sometimes referred to as UPSERT) allows a way to join two tables or views by combining update, insert and delete operations into a single statement. The merge is a DML statement that is especially useful in data warehouse environments, and in the Extraction-Transformation-Loading (ETL) cycle, where transaction tables are merged into bigger warehouse tables.
Depending on the condition you specified, a merge statement can have the following effect.
- Updates rows in target table with rows from source table that match the condition.
- Inserts new rows into target table from source that do not match the condition.
- Deletes rows from target table based on the condition.
You must specify at least one clause like insert, update, or delete, otherwise MERGE statement will fail. However, you cannot use more than two of the previous effects, because the delete clause and the update clause are mutually exclusive.
Table 1 shows an example of how the MERGE statement works. In this example, contacts is the target table and validated is used as the source table. The current customer contacts records merged with newly validated records. If the customer contact has not been validated then it is deleted from contacts table, otherwise update the record.
Table 1. Examples of MERGE statements
|MERGE||The MERGE keyword and any optimizer directives if needed.|
|INTO contacts AS t||The INTO clause specifies the target table or view.|
|USING validated AS s||The USING clause represents the source table or view.|
|ON c.cust_id = v.cust_id||The ON clause is where you supply the join between the source and target tables.|
|WHEN NOT MATCHED THEN DELETE||The 'WHEN NOT MATCHED' clause instructs what to do on target table when matching records not found in the source table. Here record from target table will be deleted.|
|WHEN MATCHED THEN UPDATE SET (t.cust_id, t.email) = (s.cust_id, s.email);||The 'WHEN MATCHED' clause instructs what to do on target table when you already have a matching record in source and target table. Here records on target table get updated based on the value in source table.|
You can specify optimizer directives in the MERGE statement to specify how the source and target tables are joined, or to control other aspects of the execution plan. Some of the directives that can be used are USE_NL, USE_HASH, AVOID_HASH, FULL, AVOID_FULL, INDEX, ORDERED, and so on. In a high-availability cluster configuration, you can issue the MERGE statement from a primary server or from an updatable secondary server.
Any constraints on the target table are enforced during MERGE operations. If an error occurs while the MERGE statement is executing, then the entire statement is rolled back.
Triggers can be defined on source and/or target tables. But during the merge operation, only insert, update, and delete triggers are actionable. The select triggers are ignored. Depending on the MERGE statement, the insert, update, and delete triggers are activated before and after events. The for-each-row trigger is activated for each row that is inserted, updated, or deleted. Therefore during insert operation, only for-each-row insert triggers on the target table are activated. Similarly, during update and delete operations, only for-each-row update or for-each-row delete triggers on the target table are activated.
The MERGE statement supports distributed queries with the restriction that the target table cannot be a remote table. You can use remote table, synonym, and views references in the sub-queries within the SET and VALUES clauses. There are additional restrictions with target tables that it cannot be a virtual table, system catalog table, external table, view on which an instead of trigger defined, read-only view, pseudo-table with memory resident objects, and so on.