Home > Blogroll, sql > MERGE syntax

MERGE syntax

May 14th, 2007
Goto comments Leave a comment

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

  1. vidya
    May 14th, 2007 at 17:58 | #1
    Reply | Quote

    Thanks Laurent …intuitive and extremely useful (both for db and ETL developers)

  2. Jeff Moss
    May 14th, 2007 at 18:58 | #2
    Reply | Quote

    …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

  3. Laurent Schneider
    May 14th, 2007 at 19:12 | #3
    Reply | Quote

    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

  4. Laurent Schneider
    May 15th, 2007 at 11:39 | #4
    Reply | Quote

    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);

  5. Hal Armstrong
    September 5th, 2007 at 23:56 | #5
    Reply | Quote

    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?

  6. Laurent Schneider
    September 6th, 2007 at 10:05 | #6
    Reply | Quote

    exp/imp? or truncate than insert as select?

  1. No trackbacks yet.