Merge is used to combine one or more DML statements into one. Merge command introduced in Oracle 9i.

Syntax :-

merge into tablename
using (select …….)
on (join condition )
when not matched then
[insert/delete/update] command
when matched then
[insert/delete/update] command;

Example :-

Consider below scenario.
An Application try to add/update an employee details.Application has the Employee Name and Number but not sure details added to the system.

SQL> desc emp;
Name                                      Null?    Type
—————————————– ——– ————-
EMPNO                                     NOT NULL NUMBER(10)
ENAME                                     NOT NULL VARCHAR2(50)

SQL> select * from emp;

EMPNO ENAME
———- ————–
10        Bill
11         Solomon

Create or replace procedure process_emp
(
p_empno emp.empno%type,
p_ename emp.ename%type
)
is
l_count pls_integer;
Begin

select count(1) into l_count from emp
where empno = p_empno;
Exception
When no_data_found then
l_count := 0;
End;

If l_count = 0 then
insert into emp(empno,ename) values(p_empno,p_ename);
else
update emp set ename = p_ename where empno = p_empno;
end if;
commit;
End process_emp;

I am re-writing above procedure with merge command

Create or replace procedure process_emp_merge
(
p_empno emp.empno%type,
p_ename emp.ename%type
)
is
l_count pls_integer;
Begin
merge into emp e
using (select p_empno as empno,p_ename as ename from dual) d
on (e.empno = d.empno)
when not matched then
insert (empno,ename) values(d.empno,d.ename)
when matched then
update set ename = d.ename where empno = d.empno;
commit;
End process_emp_merge;

Running both procedure

Begin
process_emp(12,’Mark’);
Process_emp_merge(13,’John’);
End;
/

PL/SQL procedure successfully completed.

SQL> select * from emp;

EMPNO ENAME
———- ————
10      Bill
11      Solomon
12      Mark
13      John

Two Employees added. Now running again those procedure with ename in upper case

Begin
process_emp(12,’MARK’);
Process_emp_merge(13,’JOHN’);
End;
/

PL/SQL procedure successfully completed.

SQL> select * from emp;

EMPNO ENAME
———- ————
10       Bill
11       Solomon
12       MARK
 13       JOHN

Merge is very useful in setting basic configurations. ( Running sql sripts ). We can avoid duplicates upto some extend by using merge command instead of insert statements.

Limitation on Merge

1 You cannot update the column referenced in on-clause. (In above example empno )

2. Cannot use default when updating a view