Oracle MERGE

Summary: in this tutorial, you will learn how to use the Oracle MERGE statement to perform an update or insert data based on a specified condition.

Introduction to the Oracle MERGE statement

The Oracle MERGE statement selects data from one or more source tables and updates or inserts it into a target table. The MERGE statement allows you to specify a condition to determine whether to update data from or insert data into the target table.

The following illustrates the syntax of the Oracle MERGE statement:

MERGE INTO target_table 
USING source_table 
ON search_condition
    WHEN MATCHED THEN
        UPDATE SET col1 = value1, col2 = value2,...
        WHERE <update_condition>
        [DELETE WHERE <delete_condition>]
    WHEN NOT MATCHED THEN
        INSERT (col1,col2,...)
        values(value1,value2,...)
        WHERE <insert_condition>;

Let’s examine the MERGE statement in detail:

First, specify the target table (target_table) which you want to update or insert into in the INTO clause.

Second, specify the source of data (source_table) to be updated or inserted in the USING clause.

Third, specify the search condition upon which the merge operation either updates or inserts in the ON clause.

For each row in the target table, Oracle evaluates the search condition:

• If the result is true, then Oracle updates the row with the corresponding data from the source table.
• In case the result is false for any rows, then Oracle inserts the corresponding row from the source table into the target table.

The MERGE statement becomes convenient when you want to combine multiple INSERT, UPDATE, and DELETE statements in a single operation.

Because the MERGE is a deterministic statement, you cannot update the same row of the target table multiple times in the same MERGE statement.

You can add an optional DELETE WHERE clause to the MATCHED clause to clean up after a merge operation. The DELETE clause deletes only the rows in the target table that match both ON and DELETE WHERE clauses.

Oracle MERGE prerequisites

To execute the MERGE statement, you must have the INSERT and UPDATE object privileges on the source tables. If you use the DELETE clause, you must also have the DELETE object privilege on the target table.

Oracle MERGE example

Suppose, we have two tables: members and member_staging.

We insert a new row to the members table whenever we have a new member. Then, the data from the members table is merged with the data of the member_staging table.

The following statements create the members and member_staging tables:

CREATE TABLE members (
    member_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    rank VARCHAR2(20)
);

CREATE TABLE member_staging AS 
SELECT * FROM members;

The following INSERT statements insert sample data into the members and member_staging tables:

-- insert into members table    
INSERT INTO members(member_id, first_name, last_name, rank) VALUES(1,'Abel','Wolf','Gold');
INSERT INTO members(member_id, first_name, last_name, rank) VALUES(2,'Clarita','Franco','Platinum');
INSERT INTO members(member_id, first_name, last_name, rank) VALUES(3,'Darryl','Giles','Silver');
INSERT INTO members(member_id, first_name, last_name, rank) VALUES(4,'Dorthea','Suarez','Silver');
INSERT INTO members(member_id, first_name, last_name, rank) VALUES(5,'Katrina','Wheeler','Silver');
INSERT INTO members(member_id, first_name, last_name, rank) VALUES(6,'Lilian','Garza','Silver');
INSERT INTO members(member_id, first_name, last_name, rank) VALUES(7,'Ossie','Summers','Gold');
INSERT INTO members(member_id, first_name, last_name, rank) VALUES(8,'Paige','Mcfarland','Platinum');
INSERT INTO members(member_id, first_name, last_name, rank) VALUES(9,'Ronna','Britt','Platinum');
INSERT INTO members(member_id, first_name, last_name, rank) VALUES(10,'Tressie','Short','Bronze');

-- insert into member_staging table
INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(1,'Abel','Wolf','Silver');
INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(2,'Clarita','Franco','Platinum');
INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(3,'Darryl','Giles','Bronze');
INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(4,'Dorthea','Gate','Gold');
INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(5,'Katrina','Wheeler','Silver');
INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(6,'Lilian','Stark','Silver');

When updating data from the members table to member_staging table, we should perform the following actions:

• We update the rows with member id 1, 3, 4, and 6 because the rank or the last name of these members in these tables are different.
• We insert the rows with member id 7 to 10 are because these rows exist in the members table but not in the member_staging table.

In total 8 rows should be merged.

The following is the MERGE statement that performs all of these actions in one shot.

MERGE INTO member_staging x
USING (SELECT member_id, first_name, last_name, rank FROM members) y
ON (x.member_id  = y.member_id)
WHEN MATCHED THEN
    UPDATE SET x.first_name = y.first_name, 
                        x.last_name = y.last_name, 
                        x.rank = y.rank
    WHERE x.first_name <> y.first_name OR 
           x.last_name <> y.last_name OR 
           x.rank <> y.rank 
WHEN NOT MATCHED THEN
    INSERT(x.member_id, x.first_name, x.last_name, x.rank)  
    VALUES(y.member_id, y.first_name, y.last_name, y.rank);

The merge statement compares each row in the members table with each row in the member_staging table based on the values in the member_id columns (see the ON clause above).

If the values in member_id columns of both tables are equal, the MERGE statement updates the first name, last name, and rank from the members table to the member_stagingtable only if the values of first name, last name, or rank columns of both tables are different.

Otherwise, it inserts the row from the members table into the member_staging table.

Oracle returned 8 rows merged as expected.

In this tutorial, you have learned how to use the Oracle MERGE statement to update or insert data based on a specified condition.