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

Published by

Laurent Schneider

Oracle Certified Master

7 thoughts on “MERGE syntax”

  1. 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

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

  3. 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?

  4. little note…

    this update can be under some circumstances tuned using UPDATE JOINS (much faster than basic update below).

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

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>