Oracle MERGE statement is one of those powerful commands which allows you to perform INSERT and UPDATE operations in a single statement based on specific conditions.
This article will explore the MERGE command in Oracle.
Table of Contents
What is the MERGE Command
The MERGE statement in Oracle combines the INSERT and UPDATE operations into a single statement.
It compares the data in the Source Table (or staging table) with the data in the Target Table (table to be updated). Then, based on specified conditions, the MERGE statement either inserts new rows or updates existing rows in the target table.
The basic syntax of the MERGE UPDATE command in Oracle:
MERGE INTO target_table a USING source_table b ON (condition) WHEN MATCHED THEN UPDATE SET a.column1 = b.value1, a.column2 = b.value2, ... WHEN NOT MATCHED THEN INSERT (a.column1, a.column2, ...) VALUES (b.value1, b.value2, ...)
target_table: The table to be updated.
source_table: The table containing the source data.
condition: The condition used to match rows between the source and target tables.
column1, column2: The columns in the target table to be updated.
value1, value2: The new values to be set for the corresponding columns.
Example Using MERGE to UPDATE
Let's consider a practical example to demonstrate MERGE UPDATE command.
In this example, we have defined two tables:
We will update the LEAD table with existing CUSTOMER information and create when it does not exist in LEAD.
/* CREATE A SEQUENCE HERE FOR SUPORTING THE INSERT CONDITION CREATE SEQUENCE LEAD_SEQ;*/ MERGE INTO LEAD A USING (SELECT ID_CUSTOMER, NAME, EMAIL FROM CUSTOMER) B ON (B.ID_CUSTOMER = A.ID_CUSTOMER) WHEN MATCHED THEN UPDATE SET A.NAME = B.NAME, A.EMAIL = B.EMAIL WHEN NOT MATCHED THEN INSERT (A.ID_LEAD, A.NAME, A.EMAIL, A.ID_CUSTOMER) VALUES (LEAD_SEQ.NEXTVAL, B.NAME, B.EMAIL, B.ID_CUSTOMER);
In the above example, we have merged the "CUSTOMER" table into the "LEAD" table based on matching ID_CUSTOMER.
When a match is found, the corresponding NAME and EMAIL columns in the "LEAD" table are updated with the values from the "CUSTOMER" table.
When no match is found, a new record is created.
- It is essential to ensure that the conditions used in the ON clause are accurate and precise to avoid unintended updates or insertions.
- Proper indexing on columns involved in the merge condition can significantly improve performance.
The MERGE statement simplifies data manipulation tasks by combining the INSERT and UPDATE operations into a single statement.
Understanding the syntax and usage of the MERGE UPDATE command empowers Oracle developers to perform complex data updates with ease and efficiency.