MERGE example

View All Scripts Login to Run Script

Script Name MERGE example
Description This example creates item price table catalog1 to capture the price of various items. A second table is created with updated item and price information. In order to roll all the info into the original catalog1 table, merge is used, since this is a standard UPDATE-else-INSERT task.
Area SQL General
Contributor Mike Hichwa (Oracle)
Created Monday October 05, 2015

Statement 1

create the catalog1 table with item/price pairs. Each item has an id.

create table catalog1 (id number(3), item varchar2 (20), price number(6))

Table created.

Statement 2

insert into catalog1 values(1, 'laptop', 800)

1 row(s) inserted.

Statement 3

insert into catalog1 values(2, 'iphone', 500)

1 row(s) inserted.

Statement 4

insert into catalog1 values(3, 'camera', 700)

1 row(s) inserted.

Statement 5

select * from catalog1

 
ID ITEM PRICE
1 laptop 800
2 iphone 500
3 camera 700

3 rows selected.

ID	ITEM	PRICE
1	laptop	800
2	iphone	500
3	camera	700

Statement 6

create a new table with different prices and a new item

create table catalog2 (id number(3), item varchar2 (20), price number(6))

Table created.

Statement 7

insert into catalog2 values(1, 'laptop', 899)

1 row(s) inserted.

Statement 8

insert into catalog2 values(2, 'iphone', 599)

1 row(s) inserted.

Statement 9

insert into catalog2 values(5, 'video camera', 799)

1 row(s) inserted.

Statement 10

select * from catalog2

 
ID ITEM PRICE
1 laptop 899
2 iphone 599
5 video camera 799

3 rows selected.

ID	ITEM	PRICE
1	laptop	899
2	iphone	599
5	video camera	799

Statement 11

Merge all the new info into the original catalog1 table

MERGE INTO catalog1 s1 USING catalog2 s2 ON (s1.id = s2.id) 
WHEN MATCHED THEN UPDATE SET s1.price = s2.price  
WHEN NOT MATCHED THEN INSERT (id, item, price) values (s2.id, s2.item, s2.price)

3 row(s) updated.

Statement 12

select * from catalog1

 
ID ITEM PRICE
1 laptop 899
2 iphone 599
3 camera 700
5 video camera 799

4 rows selected.

ID	ITEM	PRICE
1	laptop	899
2	iphone	599
3	camera	700
5	video camera	799

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database