Let us learn Merge Statement In Oracle

Introduction

A Merge Statement is a DML statement that is use for performing the Insert / Update or Delete statement to the target table based on the conditions supplied. It is introduce from Oracle 9i

Straight to Experiment

Let us see an example to understand this one

First let us create a source table as under

CREATE TABLE tblStudents
(
	StudentID INT NOT NULL,
	StudentName VARCHAR2(15),
	Marks1	INT,
	Marks2	INT,
	Marks3	INT,
	Marks4	INT
);

Insert into tblStudents (StudentID,StudentName,Marks1,Marks2,Marks3,Marks4) Values(1,'A.Raman',45,50,30,88);
Insert into tblStudents (StudentID,StudentName,Marks1,Marks2,Marks3,Marks4) Values(2,'B.Sekhar',56,84,99,100);
Insert into tblStudents (StudentID,StudentName,Marks1,Marks2,Marks3,Marks4) Values(3,'C.Das',32,22,78,34);
Insert into tblStudents (StudentID,StudentName,Marks1,Marks2,Marks3,Marks4) Values(4,'D.Nath',58,67,11,55);
Insert into tblStudents (StudentID,StudentName,Marks1,Marks2,Marks3,Marks4) Values(5,'E.Elizabeth',99,92,69,89);

Commit;

SQL> Select * from tblStudents;

 STUDENTID STUDENTNAME         MARKS1     MARKS2     MARKS3     MARKS4
---------- --------------- ---------- ---------- ---------- ----------
         1 A.Raman                 45         50         30         88
         2 B.Sekhar                56         84         99        100
         3 C.Das                   32         22         78         34
         4 D.Nath                  58         67         11         55
         5 E.Elizabeth             99         92         69         89
         6 F.Faran                 50         60         70         80

6 rows selected.

Then let us create a target table as under

CREATE TABLE tblStudentsTarget
(
	StudentID INT NOT NULL,
	StudentName VARCHAR2(15),
	Marks1	INT,
	Marks2	INT,
	Marks3	INT,
	Marks4	INT
);

Insert into tblStudentsTarget (StudentID,StudentName,Marks1,Marks2,Marks3,Marks4) Values(1,'A.Raman',45,0,30,0);
Insert into tblStudentsTarget (StudentID,StudentName,Marks1,Marks2,Marks3,Marks4) Values(2,'B.Sekhar',0,0,0,0);

Commit;

SQL> Select * from tblStudentsTarget;

 STUDENTID STUDENTNAME         MARKS1     MARKS2     MARKS3     MARKS4
---------- --------------- ---------- ---------- ---------- ----------
         1 A.Raman                 45          0         30          0
         2 B.Sekhar                 0          0          0          0

Now we will look into the Merge statement below. What it does is that, it takes two tables into consideration (i) A source table (e.g. tblStudents) and a target/destination table (tblStudentsTarget). The destination table will be use for modification.

SQL> MERGE 
  2  INTO tblStudentsTarget tgt
  3  USING tblStudents src
  4  ON  ( src.StudentID = tgt.StudentID )
  5  WHEN MATCHED
  6  THEN
  7  UPDATE
  8  SET   tgt.Marks1 = src.Marks1
  9   , tgt.Marks2 = src.Marks2
 10   , tgt.Marks3 = src.Marks3
 11   , tgt.Marks4 = src.Marks4
 12  WHEN NOT MATCHED
 13  THEN
 14  INSERT (tgt.StudentID,tgt.StudentName,tgt.Marks1,tgt.Marks2,tgt.Marks3,tgt.Marks4)
 15  VALUES (src.StudentID,src.StudentName,src.Marks1,src.Marks2,src.Marks3,src.Marks4);

6 rows merged.

In the above query, the Merge condition matches the StudentId in both the source and destination table. An update happens when a matching StudentId is encounter else new records are inserted. The resulting output is as under

SQL> select * from tblStudentsTarget;

 STUDENTID STUDENTNAME         MARKS1     MARKS2     MARKS3     MARKS4
---------- --------------- ---------- ---------- ---------- ----------
         1 A.Raman                 45         50         30         88
         2 B.Sekhar                56         84         99        100
         6 F.Faran                 50         60         70         80
         5 E.Elizabeth             99         92         69         89
         4 D.Nath                  58         67         11         55
         3 C.Das                   32         22         78         34

6 rows selected.

Until Oracle 10g, the Merge statement was limited to Insert and Update only. However, in Oracle 10g a major enhancement has been made to the Merge Statement where it can conditionally DELETE rows from the target dataset during an UPDATE operation.Let us see how

SQL> MERGE 
  2  INTO tblStudentsTarget tgt
  3  USING tblStudents src
  4  ON  ( src.StudentID = tgt.StudentID )
  5  WHEN MATCHED
  6  THEN
  7  UPDATE
  8  SET   tgt.StudentName = UPPER(src.StudentName)
  9  DELETE
 10  WHERE tgt.StudentName LIKE 'C%'
 11  WHEN NOT MATCHED
 12  THEN
 13  INSERT (tgt.StudentID,tgt.StudentName,tgt.Marks1,tgt.Marks2,tgt.Marks3,tgt.Marks4)
 14  VALUES (src.StudentID,src.StudentName,src.Marks1,src.Marks2,src.Marks3,src.Marks4);

6 rows merged.

In the above statement, we are updating the StudentName field of the target table and at the same time we are deleting the record from the target table whose name begins with 'C'.The result is as under

SQL> Select * from tblStudentsTarget;

 STUDENTID STUDENTNAME         MARKS1     MARKS2     MARKS3     MARKS4
---------- --------------- ---------- ---------- ---------- ----------
         1 A.RAMAN                 45         50         30         88
         2 B.SEKHAR                56         84         99        100
         6 F.FARAN                 50         60         70         80
         5 E.ELIZABETH             99         92         69         89
         4 D.NATH                  58         67         11         55

As can be figure out that the record with 'C.Das' has been deleted and the StudentName has been capitalized.

Conclusion

So in this article, we have seen how Oracle's Merge function works.Hope this will be helpful.Thanks for reading.