Oracle MERGE to UPDATE SQL

Oracle provides a robust set of SQL commands to manipulate data efficiently. MERGE to UPDATE 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 TO UPDATE command in Oracle.

Table of Contents

• What is the MERGE to UPDATE Command
• Example Usage of MERGE to UPDATE
• Additional Considerations
• Conclusion

What is the MERGE to UPDATE 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 is 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 are to be updated.
• value1, value2: The new values to be set for the corresponding columns.

Example Usage of MERGE to UPDATE

Let's consider a practical example to demonstrate using the 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      | robert.l.brown@sample.com |
| 2           | John A. Wong         | johna@sample.com          |
| 3           | Alexander W. Barclay | alexwb@sample.com         |

Customer Table Information

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              | johna@sample.com    | 2          |
| 2       | Alexander Barclay | alexwb@sample.com   | 3          |
| 3       | Robert B.         | robert.l@sample.com | 1          |

Lead Table Information

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.