Merging With Oracle

A common need arises when you have to perform a combination of insert and update operation on a single table with respect to another table. Oracle provides a 'Merge' statement for this purpose. Its a DML operation.

The following example best descirbes the concept. We have two tables emp and bonuses. We want to perform a merge operation on bonuses and emp table. We want that if we match emp and bonuses table, then if a record matches in both then the record in bonuses table gets updated and if there is no match then the record from emp table gets inserted into bonus table.

SQL> create table emp (empid number, name varchar2(30));

Table created.

SQL> insert into emp values (1,'Fahd');

1 row created.

SQL> insert into emp values (2,'DAN');

1 row created.

SQL> insert into emp values (3,'TOM');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM EMP;

EMPID NAME
---------- ------------------------------
1 Fahd
2 DAN
3 TOM

SQL> CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);

Table created.

SQL> INSERT INTO BONUSES(employee_id) (select empid from emp);

3 rows created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM bonuses;

EMPLOYEE_ID BONUS
----------- ----------
1 100
2 100
3 100

SQL> insert into emp values (4,'TOW');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM EMP;

EMPID NAME
---------- ------------------------------
1 Fahd
2 DAN
3 TOM
4 TOW

SQL> MERGE INTO BONUSES B
2 USING
3 (SELECT EMPID FROM EMP) E
4 ON (B.EMPLOYEE_ID=E.EMPID)
5 WHEN MATCHED THEN
6 UPDATE SET B.BONUS=B.BONUS+100
7 WHEN NOT MATCHED THEN
8 INSERT (B.EMPLOYEE_ID,B.BONUS) VALUES (E.EMPID,200);

4 rows merged.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM BONUSES ;

EMPLOYEE_ID BONUS
----------- ----------
1 200
2 200
3 200
4 200

Lets dissect the above merge statement line by line,

1 MERGE INTO BONUSES B

This statement tells which table is being merged, in this case its Bonuses table with alias B.

2 USING
3 (SELECT EMPID FROM EMP) E

The 'Using' statement tells us about the table from which the comparison will be made. in this case its EMP table, and we have selected empid from this table.

4 ON (B.EMPLOYEE_ID=E.EMPID)

The 'ON' statement is the filter which decides whether insert or update will be performed. In the 'ON' statement we are comparing table-to-be-merged (i.e. Bonuses) and the table from which the comparison is being made.

5 WHEN MATCHED THEN
6 UPDATE SET B.BONUS=B.BONUS+100

If the condition is true in the 'On' statement on line 4, then the update on table Bonuses will be made.

7 WHEN NOT MATCHED THEN
8 INSERT (B.EMPLOYEE_ID,B.BONUS) VALUES (E.EMPID,200);

If the condition is false in the 'On statement on line 4, then the record from emp table will be inserted in bonuses table.