merge command

Merge Command

What is Merge in SQL?

Merge Command was introduce in ORACLE 9i, the ability to conditionally update or insert into a database table. merge statement avoid separate updates, increases the performance and ease of use and is useful in data warehouse applications.

How to use merge command in SQL?

Create two tables and insert few records in first table for understanding the merge statement.

Learn how to create SEQUENCE step by step click here

Using below query create first table.

SQL> create table student(id number,name varchar2(22), score number);
Table created.

After creating first table, now i’m going to insert few records in this table.

SQL> insert into student values(&id,'&name',&A);

 Enter value for id: 1
 Enter value for name: AMIT
 Enter value for a: 232
 old 1: insert into student values(&id,'&name',&A)
 new 1: insert into student values(1,'AMIT',232)

 1 row created.

 SQL> /
 Enter value for id: 2
 Enter value for name: RAHUL
 Enter value for a: 2321
 old 1: insert into student values(&id,'&name',&A)
 new 1: insert into student values(2,'RAHUL',2321)
 
1 row created.
 
SQL> /
 Enter value for id: 3
 Enter value for name: ANKIT
 Enter value for a: 843
 old 1: insert into student values(&id,'&name',&A)
 new 1: insert into student values(3,'ANKIT',843)
 
1 row created.
 
SQL> /
 Enter value for id: 4
 Enter value for name: AJAY
 Enter value for a: 452
 old 1: insert into student values(&id,'&name',&A)
 new 1: insert into student values(4,'AJAY',452)
 
1 row created.
 
SQL> /
 Enter value for id: 5
 Enter value for name: ARUN
 Enter value for a: 543
 old 1: insert into student values(&id,'&name',&A)
 new 1: insert into student values(5,'ARUN',543)
 
1 row created.

Then commit all changes using below command.

SQL> COMMIT; 

Commit complete.

Check records which you inserted.

SQL> SELECT *FROM STUDENT;

 ID  NAME      SCORE
 --  ------   -------
 1   AMIT      232
 2   RAHUL     2321
 3   ANKIT     843
 4   AJAY      452
 5   ARUN      543

Now create second table which is have same structure like student table and also have few same records. using following steps.

SQL> CREATE TABLE STUDENT_N AS SELECT * FROM STUDENT WHERE 1=2;
 
Table created.
 
SQL> INSERT INTO STUDENT_N VALUES(&ID,'&NAME',&S);
 Enter value for id: 4
 Enter value for name: AJAY
 Enter value for s: ''
 old 1: INSERT INTO STUDENT_N VALUES(&ID,'&NAME',&S)
 new 1: INSERT INTO STUDENT_N VALUES(4,'AJAY','')
 
1 row created.
 
SQL> /
 Enter value for id: 5
 Enter value for name: ARUN
 Enter value for s: 600
 old 1: INSERT INTO STUDENT_N VALUES(&ID,'&NAME',&S)
 new 1: INSERT INTO STUDENT_N VALUES(5,'ARUN',600)

 1 row created.
 
SQL> /
 Enter value for id: 6
 Enter value for name: SACHIN
 Enter value for s: 787
 old 1: INSERT INTO STUDENT_N VALUES(&ID,'&NAME',&S)
 new 1: INSERT INTO STUDENT_N VALUES(6,'SACHIN',787)

 1 row created.

 SQL> /
 Enter value for id: 7
 Enter value for name: SHRIPAL
 Enter value for s: 504
 old 1: INSERT INTO STUDENT_N VALUES(&ID,'&NAME',&S)
 new 1: INSERT INTO STUDENT_N VALUES(7,'SHRIPAL',504)
 
1 row created.
 
SQL> /
 Enter value for id: 8
 Enter value for name: SPSINGH
 Enter value for s: 889
 old 1: INSERT INTO STUDENT_N VALUES(&ID,'&NAME',&S)
 new 1: INSERT INTO STUDENT_N VALUES(8,'SPSINGH',889)
 
1 row created.
 
SQL> COMMIT;

Commit complete.

Retrieve records from second table.

SQL> SELECT *FROM STUDENT_N;

 ID   NAME     SCORE
---   ------- ------ 
 4    AJAY
 5    ARUN     600
 6    SACHIN   787
 7    SHRIPAL  504
 8    SPSINGH  889

Following query will merge both tables, as you can see in query we compare the first table with second table, the second table is recently updated by a particular department, so we want to update student table if student table id matched with student_n table id column.

SQL> merge into student a
   using (select id,name,score from student_n) b
   on (a.id=b.id)
   when matched then
   update set a.name=b.name,
   a.score=b.score
   when not matched then
   insert (a.id,a.name,a.score)
   values(b.id,b.name,b.score);

5 rows merged.

After above command check records in student table, that was updated.

SQL> select * from student;
 
ID   NAME SCORE
 --  ------  ----- 
 1   AMIT     232
 2   RAHUL   2321
 3   ANKIT    843
 4   AJAY
 5   ARUN     600
 6   SACHIN   787
 7   SHRIPAL  504
 8   SPSINGH  889

 8 rows selected.

You Can subscribe my YouTube channel

Share this
Share

2 thoughts on “Merge Command”

Leave a Comment

Share