Oracle MERGE Statement

Use the MERGE statement to select rows from one table for update or insertion into another table. The decision whether to update or insert into the target table is based on a condition in the ON clause. It is a new feature of Oracle Ver. 9i. It is also known as UPSERT i.e. combination of UPDATE and INSERT.

For example suppose we are having sales and sales_history table with the following structure.

SALES

Prod	Month	Amount
SONY SONY SONY SONY SONY SONY	JAN FEB MAR APR MAY JUN	2200 3000 2500 3200 3100 5000

SALES HISTORY

Prod	Month	Amount
SONY SONY SONY AKAI	JAN MAR APR JAN	2000 2500 3000 3200

Now we want to update sales_history table from sales table i.e. those rows which are already present in sales_history, their amount should be updated and those rows which are not present in sales_history table should be inserted.

merge into sales_history sh

   using sales s

   on (s.prod=sh.prod and s.month=sh.month)

when matched then update set sh.amount=s.amount

when not matched then insert values (prod,month,amount);

After the statement is executed sales_history table will look like this.

SALES HISTORY

Prod	Month	Amount
SONY SONY SONY SONY AKAI SONY SONY	JAN FEB MAR APR JAN MAY JUN	2200 3000 2500 3200 3200 3100 5000