How to use merge command in ORACLE SQL step by step

It 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.

Step 1. create a table using below command then insert some records in this table.

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

Step 2. Now insert some record in this table using following command.

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.

Now select record from this table which is you created.

SQL> SELECT *FROM STUDENT;

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

Step 3.

Now create one more table which is have same structure like student table and also have some same records using below command.

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

Step 4. Now merge both table using following command, and get your output together.

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.

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.

NOTE:- For more clarity of merge statement topic please view video, please scroll.