MERGE Statement in Oracle - Syntax with examples

When we ask for what are the DML statements?, we used to say   SELECT, INSERT, UPDATE and DELETE.  But, we have one more special statement which is MERGE.  One of the most powerful DML SQL statement. 

Let us see today on  WHAT  is  /  HOW it works / SYNTAX of  / Example of  / BENEFITS of MERGE statement.



What is Merge statement first of all.. ?

MERGE statement is dynamic statement for situations; when you want to do any of the below:

           Update existing rows in a table [or]
           Insert new rows [or]
           Delete existing rows  

depending on a match condition.

Instead of achieving it by  many IF conditions and multiple Insert/Update/Delete statements, we can achieve this situation by using ONE MERGE statement. 



How MERGE statement works.. ?

To understand How MERGE statement works, let us have two tables  a) STUDENT   &   b)   STUDENT_1

SQL> select * from STUDENT;

        ID NAME              MARKS
---------- ------------ ----------
       103 Modi               650
       104 Pudin              620
       105 Trump              555

5 rows selected.

SQL> select * from STUDENT_1; 

        ID NAME              MARKS
---------- ------------ ----------
       103 Modi               690
       104 Putin              620
       105 Trump              555

       106 Talai              540

Now, we have a requirement to do the below actions. 

   a)  Mark of the student Modi to be corrected from 650 from 690

   b)  Name of the student Pudin to be corrected as Putin

   c)  New student Talai record to be inserted

   d)  No change for the student Trump

   

Can we achieve this in one single statement?.  Yes, by using MERGE. 

We will understand the syntax and arrive the SQL for this requirement below. 

MERGE statement SYNTAX :- 

MERGE into <target table>          /*  Refer Student table here*/
USING
    <source table/view/result of sub query>       /*  Refer Student_1 table here */
ON
    <match condition>
WHEN MATCHED THEN
    <update clause>          /* ANY DML */          
    <delete clause>          /* ANY DML */
WHEN NOT MATCHED THEN
    <insert clause>          /* ANY DML */

MERGE statement for the above example ( Student & Student 1 )

SQL> merge into student a
  2  using
  3    (select id, name, marks      
  4     from student_1) b
  5  on (a.id = b.id)
  6  when matched then
  7    update set a.name = b.name
  8         , a.marks = b.marks
  9  when not matched then
 10    insert (a.id, a.name, a.marks)
 11    values (b.id, b.name, b.marks);

The above highlighted yellow section is USING clause. Where we can use any of the below 

    a)  Result of select statement  ( as we used above )  OR

    b) Table name  OR

    c)  View name

Benefits of MERGE statement:

1. Can achieve multiple DML in ONE Merge statement
2. Can avoid looping through for each record 
3. Can avoid multiple IF conditions
4. Parsing through the tables for multiple times will be reduced to ONE

< Feel free to comment below with your feedback >