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

7 thoughts on “MERGE syntax”

  3. Laurent Schneider Post author

    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 Post author

    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

    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?

  7. jiri

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

Comments are closed.