Merge Query in Oracle SQL

  • Use the merge statement to select rows from one or more source for update or insert into table or view.
  • You can specify condition to determine whether to update or insert into the target table or view.
  • It is conventional way to combine multiple operations

Example : We have a source table and a target table we will reflect the change or source table into the target table using merge query.

Creating DataDump

create table emp_src(emp_no number, ename varchar2(10), sal number);
insert into emp_src values (1,'Tyson',1000);
insert into emp_src values (2,'Justin',2000);
insert into emp_src values (3,'Martin',3000);
insert into emp_src values (4,'Jake',4000);


create table emp_trt(emp_no number, ename varchar2(10), sal number);
insert into emp_trt values (1,'Tyson',1000);
insert into emp_trt values (2,'Justin',2000);
insert into emp_trt values (3,'Martin',3000);
insert into emp_trt values (4,'Jake',4000);

In above queries both the table emp_src and emp_trt are exactly same, now we will make an insert and update into the src table

insert into emp_src values (5,'Fade',5000);
update emp_src set sal='3500' where emp_no=3;

Now if we have to make an insert and update manually into the target table it will look something like below but note instead of using this we are going to use merge statement.

update emp_trt
set sal = (select emp_src.sal from emp_src where emp_src.emp_no=emp_trt.emp_no);

insert into emp_trt
select * from emp_src where emp_src.emp_no not in (select emp_no from emp_trt);

But instead of update and insert we are going to perform this with a single merge statement below

merge into emp_trt
using emp_src
on (emp_trt.emp_no = emp_src.emp_no)
when matched then 
    update set sal = emp_src.sal
when not matched then
    insert values (emp_src.emp_no,emp_src.ename,emp_src.sal);

Leave a Comment