The MERGE Statement in oracle

The MERGE Statement
The MERGE statement is a single command that combines the ability to update or insert rows into a table
by conditionally deriving the rows to be updated or inserted from one or more sources
This allows you to avoid issuing multiple INSERT, UPDATE, and DELETE statements.

The syntax for the MERGE statement is:

MERGE <hint>
INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
DELETE <where_clause>
WHEN NOT MATCHED THEN <insert_clause>
[LOG ERRORS <log_errors_clause> <reject limit <integer | unlimited>];

 MERGE Statement Example


SQL> create table dept60_bonuses
2 (employee_id number
3 ,bonus_amt number);
Table created.
SQL> insert into dept60_bonuses values (103, 0);
1 row created.
SQL> insert into dept60_bonuses values (104, 100);
1 row created.
SQL> insert into dept60_bonuses values (105, 0);
1 row created.
SQL> commit;
Commit complete.
SQL> select employee_id, last_name, salary
2 from employees
3 where department_id = 60 ;
EMPLOYEE_ID LAST_NAME SALARY
--------------- ------------------------- ---------------
103     Hunold     9000
104     Ernst     6000
105     Austin     4800
106     Pataballa  4800
107     Lorentz        4200

SQL> select * from dept60_bonuses;
EMPLOYEE_ID BONUS_AMT
--------------- ---------------
103     0
104     100
105     0

3 rows selected.


SQL> merge into dept60_bonuses b
2 using (
3 select employee_id, salary, department_id
4 from employees
5 where department_id = 60) e
6 on (b.employee_id = e.employee_id)
7 when matched then
8 update set b.bonus_amt = e.salary * 0.2
9 where b.bonus_amt = 0
10 delete where (e.salary > 7500)
11 when not matched then
12 insert (b.employee_id, b.bonus_amt)
13 values (e.employee_id, e.salary * 0.1)
14 where (e.salary < 7500);

4 rows merged.
SQL> select * from dept60_bonuses;
EMPLOYEE_ID BONUS_AMT
--------------- ---------------
104     100
105     960
106     480
107     420

4 rows selected.

SQL> rollback;
Rollback complete.

The MERGE accomplished the following:
• Two rows were inserted (employee_ids 106 and 107).
• One row was updated (employee_id 105).
• One row was deleted (employee_id 103).
• One row remained unchanged (employee_id 104).
Without the MERGE statement, you would have had to write at least three different statements to
complete the same work.