Oracle 9 Merge Statement

This example shows the Oracle 9 version of the merge statement. We start with a master table containing 2 rows. The 1st row is for Andrew, who has a salary of £10000. The 2nd row is for Colin, who has a salary of £13000.

SQL> create table master
  2  (first_name varchar2(10),
  3  salary    number)
  4  /

Table created.

SQL> insert into master
  2  values ('Andrew', 10000)
  3  /

1 row created.

SQL> insert into master
  2  values ('Colin', 13000)
  3  /

1 row created.

SQL> select * from master
  2  order by first_name
  3  /

FIRST_NAME     SALARY
---------- ----------
Andrew          10000
Colin           13000

SQL>

Then we create a table of updates. The 1st update changes Andrew's salary to £11000. The 2nd update adds a new employee called Brian, who has a salary of £12000.

SQL> create table updates
  2  (first_name varchar2(10),
  3  salary    number)
  4  /

Table created.

SQL> insert into updates
  2  values ('Andrew', 11000)
  3  /

1 row created.

SQL> insert into updates
  2  values ('Brian', 12000)
  3  /

1 row created.

SQL> select * from updates
  2  order by first_name
  3  /

FIRST_NAME     SALARY
---------- ----------
Andrew          11000
Brian           12000

SQL>

The merge statement applies the updates. There is no row for Colin in the updates table so his salary does not change:

SQL> merge into master m
  2  using updates u
  3  on (m.first_name = u.first_name)
  4  when matched then
  5  update set m.salary = u.salary
  6  when not matched then
  7  insert (first_name, salary)
  8  values (u.first_name, u.salary)
  9  /

2 rows merged.

SQL> select * from master
  2  order by first_name
  3  /

FIRST_NAME     SALARY
---------- ----------
Andrew          11000
Brian           12000
Colin           13000

SQL>