Working with Merge Statement

What is the use of Merge Statement: Whenever we want to perform insert,update,delete based on specific condition in single statement then we will use Merge statement.


It selects data from one or more source tables and insert/update/delete will be done in target table.

MERGE INTO target_table 
USING source_table 
ON search_condition
    WHEN MATCHED THEN
        UPDATE SET col1 = value1, col2 = value2,...
        WHERE <update_condition>
        DELETE WHERE <delete_condition>
    WHEN NOT MATCHED THEN
        INSERT (col1,col2,...)
        values(value1,value2,...)

        WHERE <insert_condition>;

Here Target_table is whichever table we want to insert/update/delete.
and source table is which table data we are going to use for insert/update/delete operation and also to specify search condition.

Based on search condition --> if it true then update and delete operation will be done and if its false then insert operation will be done.

Eg 1:
create table EMPLOYEE_DET
(
  EMPNO   NUMBER(4),
  ENAME   VARCHAR2(10),
  DEPTNO  NUMBER(2),
  ADDRESS VARCHAR2(200)

);

create table HR_RECORDS
(
  EMPNO   INTEGER,
  ENAME   VARCHAR2(10),
  DEPTNO  NUMBER(2),
  ADDRESS VARCHAR2(200)
)

Requirement is update address column in emp table from hr_records table and insert in target if any new employee details exist in hr table.


SQL> SELECT * FROM employee_det;

EMPNO ENAME      DEPTNO ADDRESS
----- ---------- ------ -------------------
  101 VENKY          10      Hyderabad
  102 TEJA             10      CHENNAI
  103 SATISH         20     HYDERABAD

SQL> SELECT * FROM HR_RECORDS;

  EMPNO ENAME         DEPTNO ADDRESS
------- ---------- ------ ----------------
    101 VENKY1          10    BANGALORE
    102 TEJA              10    NCR
    103 SATISH          20    DELHI
    104 NIKITH          30    HYDERABAD
    105 RAJU             30    CHENNAI



Above one is our tables data , now we are going to finish our requirement using below MERGE statement.

SQL> MERGE INTO EMPLOYEE_DET ed
  2  USING HR_RECORDS hr
  3  on (hr.empno= ed.empno)
  4  WHEN MATCHED THEN
  5    UPDATE SET ED.ADDRESS = HR.ADDRESS
  6  WHEN NOT MATCHED THEN
  7    INSERT (EMPNO ,ENAME, DEPTNO ,ADDRESS)
  8    VALUES (HR.EMPNO ,HR.ENAME, HR.DEPTNO ,HR.ADDRESS);

Done

SQL> SELECT * FROM EMPLOYEE_DET;

 EMPNO ENAME      DEPTNO ADDRESS
----- ---------- ------ ---------
  101   VENKY          10 BANGALORE
  102   TEJA             10 NCR
  103   SATISH         20 DELHI
  105   RAJU             30 CHENNAI
  104   NIKITH          30 HYDERABAD

In single merge statement , address and new records got updated in employee_det table.



  SQL> create table merge_test(npa integer, nxx integer,m_start varchar2(20),m_end varchar2(20));

Table created

SQL> insert into merge_test values( 800,123,'0000','0004');

1 row inserted

SQL> insert into merge_test values( 800,123,'0004','9999');

1 row inserted

SQL> insert into merge_test values( 900,124,'0000','0005');

1 row inserted

SQL> insert into merge_test values( 900,124,'0005','0099');

1 row inserted

SQL> select * from merge_test;

NPA        NXX     M_START              M_END
--- ----------     -------------------- ---------
800        123     0000                 0004
800        123     0004                 9999
900        124     0000                 0005

900        124     0005                 0099

Lets say we have above data and our requirement is whenever NPA and NXX has started with new m_start value then we need to take that record's m_end value and update it in first record and delete that new record.

Sample output as below:

NPA        NXX     M_START              M_END
--- ----------     -------------------- ---------
800        123     0000                 9999

900        124     0000                 0099

SQL> MERGE INTO MERGE_TEST MT

  2  USING MERGE_TEST MT1

  3  ON( MT.NPA= MT1.NPA AND MT.ROWID <> MT1.ROWID)

  4  WHEN MATCHED THEN

  5    UPDATE SET MT.M_END  = MT1.M_END

  6    DELETE  WHERE MT.M_START= MT1.M_END

  7  ;

Done

SQL> select * from merge_test;

 NPA                           NXX M_START              M_END

--------------------------------------- -------------------------

800                               123    0000                 9999

900                               124    0000                 0099

as you observed ,we are checking condition on NPA column and rowid as well ,we took ROWID to avoid ora-30326 error.

click here to check what's the use of rowid in merge statement.

and if condition is matched we are updating and deleting records.