Laurent Schneider

MERGE syntax

With merge you can insert, update or both

I want to import changes from one table in another table.

Table T1:

ID	NAME
1	Apple
2	Orange

Table T2:

ID	NAME
1	Pineapple
3	Lemon

To insert the missing row from T2 into T1, I could need the following subquery with INSERT.

insert into t1 
select * from t2 
where not exists (
  select * from t1 
  where t1.id=t2.id);

The same code would be done in Merge with

merge into t1 using t2 
on (t1.id=t2.id) 
when not matched then 
  insert values(t2.id,t2.name);

And what about updating the T1 table with the T2 values

update t1 
set (name)=(select name from t2 where id=t1.id)
where id in (select id from t2);

with merge :

merge into t1 using t2 on (t1.id=t2.id) 
when matched then 
  update set name=t2.name;

Quite nice, is not it! I find this syntax very intuitive.

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

merge into t1 using t2 on (t1.id=t2.id) 
when not matched then 
  insert values(t2.id,t2.name)
when matched then 
  update set name=t2.name;

ID	NAME
1	Pineapple
2	Orange
3	Lemon

This entry was posted on Monday, May 14th, 2007 at 14:55 and is filed under oracle, sql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Post to : del.icio.us - Digg it - Furl - reddit - Spurl

Put your code in <code> and </code> tags

6 Responses to “MERGE syntax”

vidya Says:
May 14th, 2007 at 17:58
Thanks Laurent …intuitive and extremely useful (both for db and ETL developers)
Jeff Moss Says:
May 14th, 2007 at 18:58
…can also do DELETE operations too…as I’m sure you’re aware Laurent.

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9016.htm#i2081030
Laurent Schneider Says:
May 14th, 2007 at 19:12
well, the can only delete when matched, I would be more interested in deleting <2;ORANGE> or betting updating <2;ORANGE> to <2;ORANGE;deleted>

thanks for the comments
Laurent Schneider Says:
May 15th, 2007 at 11:39
merge into t1 using t2 on (t1.id=t2.id)
when matched then
update set name=t2.name;

to update only the rows that have changed, I add a where clause
```
merge into t1 using t2 on (t1.id=t2.id)
when matched then
  update set name=t2.name
  where lnnvl(name=t2.name);
```
Hal Armstrong Says:
September 5th, 2007 at 23:56
I have issues where I have several development databases and some are up to date and some are behind. Merge can be used to write scripts to “Force this data, whether by INSERT or UPDATE”.

Example:
MERGE INTO po_discount_reason dest
USING (SELECT 40 AS PO_DISCOUNT_REASON_CD FROM dual) sou
ON (sou.PO_DISCOUNT_REASON_CD = dest.PO_DISCOUNT_REASON_CD)
WHEN MATCHED THEN
UPDATE SET
rank = 40,
name = ‘Temporary Discount’,
description = ‘Temporary Discount’
WHEN NOT MATCHED THEN
INSERT (
po_discount_reason_cd, rank, name, description )
VALUES (
40, 40, ‘Temporary Discount’, ‘Temporary Discount’);

The above works. Is there any simpler way to do the same?
Laurent Schneider Says:
September 6th, 2007 at 10:05
exp/imp? or truncate than insert as select?

Use <code> and </code> to post code

MERGE syntax

6 Responses to “MERGE syntax”

Leave a Reply