Oracle MERGE STATEMENT (9i/10g/11g)

15 Jan

The MERGE statement simply enables us to either UPDATE or INSERT a row into a target table in one statement. You simply tell Oracle your rules for determining whether a target row should be UPDATED or INSERTED from the source, and Oracle does the rest.

In order to make clear understanding we are creating two tables here one will act as a source table and other as a target table.

Source Table :

SQL> Create table employeesSource (empno number(5), ename varchar2(30)) ;

SQL> Insert into employeesSource values (1,’Hafeez’) ;

SQL> Insert into employeesSource values (2,’Najeeb’) ;

SQL> Insert into employeesSource values (3,’Muneeb’) ;

SQL> Insert into employeesSource values (4,’Aziz’) ;

SQL> Insert into employeesSource values (5,’Haseeb’) ;

Commit ;

Target Table :

SQL> Create table employeesTarget (empno number(5), ename varchar2(30), status varchar2(10)) ;

SQL> Insert into employeesTarget values (1, ‘Ahmad’,null) ;

SQL> Insert into employeesTarget values (3, ‘Ali’,null) ;

To insert the missing row from employeesSource into employeesTarget, we need the following subquery with INSERT statement.

Without MERGE Statement:

SQL> insert into employeesTarget select * from employeesSource where not exists (select * from employeesTarget where employeesTarget.empno= employeesSource.empno) ;

With MERGE Statement:

SQL> Merge into employeesTarget using employeesSource on (employeesTarget.empno= employeesSource.empno) when not matched then insert values(employeesSource.empno, employeesSource.ename,’INSERTED’);

Now for updating the employeesTarget table with the employeesSource values

Without MERGE Statement:

update employeesTarget set status = ‘UPDATED’, (ename)=(select ename from employeesSource where empno= employeesTarget.empno) where empno in (select empno from employeesSource);

With MERGE Statement:

SQL> merge into employeesTarget using employeesSource on (employeesTarget.empno= employeesSource.empno) when matched then update set status = ‘UPDATED’, ename= employeesSource.ename;

Merge can also combine insert and update in once, which make it more stable (only one statement) and more performant (table employeesSource is read only once).

SQL> merge into employeesTarget using employeesSource on (employeesTarget.empno= employeesSource.empno) when not matched then insert values(employeesSource.empno, employeesSource.ename,’INSERTED’) when matched then update set status = ‘UPDATED’, ename= employeesSource.ename;

Note: I hope this article will help you to understand the MERGE Statment. You can practice it by inserting, different records, and perform merge statement with different merge conditions.

source : internet

4 Responses to “Oracle MERGE STATEMENT (9i/10g/11g)”

  1. declan February 5, 2008 at 12:43 pm #

    very very gud, its after getting my head sum what around it.

  2. Aazam July 20, 2008 at 6:28 am #

    Thank Oracle Admin, It make my life a little easier.

  3. pawan modi September 11, 2008 at 6:48 pm #

    really nice…..!!!!!!!!

  4. reader October 1, 2008 at 1:25 pm #

    Oracle returns an error statement after i tried the merge command:

    “unknown command ‘MERGE'”

    WHY O WHY??

    my version is oracle9i

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: