MERGE command in Oracle

Swadhin Ray
MERGE command is used to merge two tables like from a source to target table.
 In general the target table exists as acceptable data in the database where as the source table is really a table which containing the data which is not necessarily in the database yet, whereas some of the rows could be updated or inserted into the target table as new rows.

MERGE is used to insert new rows into the target table and update existing rows between the source and the target table.

MERGE statement syntax:

MERGE INTO [schema.]table [alias]
                      USING {[schema.]table|view|(subquery)} [alias]
                      ON (column = column [,column=column ...])
                      WHEN MATCHED THEN 
                        UPDATE SET {column ={column | expression| DEFAULT}}
                          [,column =...]
                      WHEN NOT MATCHED THEN 
                        INSERT (column [,column])
                          VALUES ({column |expression | DEFAULT}[,column ....])

Open in new window

From the above syntax we can see that we use like “MERGE INTO” the target table “USING” the source table and with the join condition at “ON”.

For example I have created one sample table as SOURCE_TABLE as below:

SQL> create table SOURCE_TABLE
                      id number primary key,
                      COL1 varchar2(10),
                      COL2 number,
                      COL3 number

Open in new window

Create Source Table
Insert few records so that we can see how we can user merge on the target table. 

Open in new window

                      insert into source_table values(1,'A',1,1);
                      insert into source_table values(2,'A',2,1);
                      insert into source_table values(3,'A',3,1);
                      insert into SOURCE_TABLE values(4,'B',1,1);

Open in new window

Insert into Source Table
Here is the sample data on our source table i.e. “SOURCE_TABLE” :

Sample data in Source Table
Now let us create the target table as below:

SQL> CREATE TABLE target_table
                          id NUMBER PRIMARY KEY ,
                          col1 VARCHAR2( 10 ) ,
                          COL2 NUMBER ,
                          COL3 NUMBER ,
                          MODIFIED_BY VARCHAR2( 100 ) ,
                          CHANGE_DATE TIMESTAMP ,
                          change_type VARCHAR2( 100 )

Open in new window

Create Target Table
In  the target table I also used few additional columns which will help me to identify which user has updated or inserted  and when, along with that I also kept to have a track on what type done on the target table like was the row inserted or it’s been updated.
We can enhance this by implementing few more logic as per our business needs.

So now let us also insert few records into our target table which will tell us like whether it was an update or an insert by our merge command which we are going to use.

Run the below INSERT query on the target table :

                      insert into TARGET_TABLE values(2,'A',8,1,'OTHER USER',sysdate-40,'INSERT');
                      insert into TARGET_TABLE values(3,'A',3,1,'OTHER USER',sysdate-40,'INSERT');

Open in new window

Insert into Target Table
So the data on my target table will look like as below:

Sample data in Target Table
From the above insert query you can see that I have used 'OTHER USER' on “MODIFIED_BY” column which specifies that it was been inserted by some other user (just for an example) and SYSDATE-40 on “CHANGE_DATE” column just have the track on the modification date. Finally the “CHANGE_TYPE” column, I made as ‘INSERT’ to mark as these are the rows which are inserted as new records but no updates are done.

Before going to MERGE command I will be using a simple function will return me the OS user name who has logged in:

SQL> select sys_context('USERENV','OS_USER') from dual; 

Open in new window

Function to return OS user
So the below is my merge command for getting the records from my source table to target table:

SQL> MERGE INTO target_table tt
                      USING SOURCE_TABLE st
                      ON ( =
                      WHEN MATCHED THEN 
                        update set 
                             tt.col1        =st.col1,
                             tt.COL2         = ST.COL2, 
                      			 TT.COL3         = ST.COL3,  
                      			 TT.MODIFIED_BY  =sys_context('USERENV','OS_USER'),
                      			 TT.CHANGE_DATE  = sysdate,
                      			 tt.CHANGE_TYPE  = 'UPDATE'
                      when not matched then 
                        insert (id,COL1,COL2,COL3,MODIFIED_BY,CHANGE_DATE,CHANGE_TYPE )
                          values (,ST.COL1,ST.COL2,ST.COL3,sys_context('USERENV','OS_USER'),systimestamp,'INSERT');

Open in new window

Merge Command
Commit after executing the merge.

Now we can see the changes as below:

Final Output
Now we can have a track for like who had modified it at what time and we will also have a track like if it is inserted a new record or it’s an update.
Swadhin Ray

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.