Merge Statement

- MERGE statement can be used to conditionally insert or update data depending on it's presence.
- This method reduces table scans and can perform the operation in parallel.
- Consider the following example where data from the HR_RECORDS table is merged into the EMPLOYEES table:

Syntax
MERGE  INTO 
USING ON (condition)
WHEN MATCHED THEN 
UPDATE 
WHEN NOT MATCHED THEN 
INSERT;


SQL> SELECT * FROM mer_data;
ID EMPNAME              DESIGNATION
----- -------------------- --------------------
10 Muthu                Sys Analyst
20 Kumar                Programmer
30 Raman                Sys Analyst
31 Raj                  Project Manager
32 Alen                 Programmer

5 row selected.

SQL> SELECT * FROM ins_data;
ID   EMPNAME               DESIGNATION
-----  --------------------   -----------------
  20  Raman                 xxx
1 row selected.


SQL> MERGE INTO ins_data i
USING mer_data m
ON ( m.id = i.id )
WHEN MATCHED THEN
 UPDATE SET empname = m.empname, designation = m.designation
WHEN NOT MATCHED THEN
 INSERT VALUES (m.id, m.empname, m.designation);


    ID  EMPNAME              DESIGNATION
---------- -------------------- --------------------
     20  Kumar                Programmer
     30  Raman                Sys Analyst
     32  Alen                 Programmer
     31  Raj                  Project Manager
     10  Muthu                Sys Analyst

5 rows selected.