Oracle MERGE Statement SQL

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 to UPDATE Command
• Example Using MERGE to UPDATE
• Additional Considerations
• Conclusion

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:

CREATE TABLE CUSTOMER (
	ID_CUSTOMER NUMBER
    , NAME VARCHAR(100)
    , EMAIL VARCHAR(100)
);

SELECT * FROM CUSTOMER;

ID_CUSTOMER  NAME                   EMAIL
-----------  ---------------------  -------------------------
1            Robert L. Brown        [email protected]
2            John A. Wong           [email protected]
3            Alexander W. Barclay   [email protected]

CREATE TABLE LEAD (
	ID_LEAD NUMBER
    , NAME VARCHAR(200)
    , EMAIL VARCHAR(100)
    , ID_CUSTOMER NUMBER
);

SELECT * FROM LEAD;

ID_LEAD      NAME                   EMAIL                      ID_CUSTOMER
-----------  ---------------------  -------------------------- -----------
1            John                   [email protected]           2
2            Alexander Barclay      [email protected]          3
3            Robert B.              [email protected]  1

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.

Additional Considerations

• 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.

Conclusion

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.