What are the uses of MERGE?

MERGE is used to combine multiple DML statements into one.
Syntax : merge into tablename
using(query)
on(join condition)
when not matched then
[insert/update/delete] command
when matched then
[insert/update/delete] command

Merging Rows

The MERGE statement inserts or updates rows in one table by using data from another table. Each row is inserted or updated in the target table depending on an equijoin

condition.


The example shown matches the employee_id in the  tab_new table to the employee_id in the employees table. If a match is found, the row is updated to match the row in the

employees table. If the row is not found, it is inserted into the tab_new table.

SQL> create table tab_new as select employee_id eid,first_name fname,last_name lname from employees;

SQL> desc tab_new;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EID                                                NUMBER(6)
 FNAME                                         VARCHAR2(20)
 LNAME                                     NOT NULL VARCHAR2(25)

SQL> truncate table tab_new;

SQL> insert into tab_new values(101,'nasim','khan');

SQL> insert into tab_new values(102,'Easha','Deo');
SQL> commit;

SQL> insert into tab_new values(103,'rafat','shaikh');

SQL> commit;

   merge into tab_new t
   using employees e
    on (t.eid=e.employee_id)
    when matched then
    update set
    t.fname=e.first_name,
    t.lname=e.last_name
   when not matched then
  insert values (e.employee_id,e.first_name,e.last_name)

SQL> select * from tab_new;
       EID FNAME                LNAME
---------- -------------------- -------------------------
       101 nasim                khan
       102 Eash                Deo
       103 rafat                shaikh




select * from tab_new;

       EID FNAME                LNAME
---------- -------------------- -------------------------
       101 Neena                Kochhar
       102 Lex                  De Haan
       103 Alexander            Hunold
       198 Donald               OConnell
       199 Douglas              Grant
: