View source for Merge update table with data from other table

You want to update one table with data from another table, based on some relationship that is close to a one-to-one relation. You can do this with a subquery but that can be very slow:

<syntaxhighlight lang=sql>
UPDATE TABLE1 t1 SET (DATA1,DATA2)=
  ( SELECT DATA1,DATA2 FROM TABLE2 t2
    WHERE t2.FK=t1.ID ) 
  WHERE ID in (SELECT FK FROM TABLE2)
  ;
</syntaxhighlight>

It seems that in the above query the subquery on TABLE2 is executed for every row of TABLE1. That would mean count(TABLE1)+1 tablescans, while it should be possible in two tablescans: join TABLE1 and TABLE2 and update TABLE1. Since Oracle 9 we have the MERGE update query for these cases:

<syntaxhighlight lang=sql>
MERGE INTO TABLE1 t1
  USING ( SELECT FK,DATA1,DATA2 FROM TABLE2 ) t2
    ON ( t1.ID=t2.FK )
  WHEN MATCHED THEN UPDATE SET 
    DATA1=t2.DATA1
    , DATA2=t2.DATA2
    ;
</syntaxhighlight>

Note: the ID and FK columns should be indexed

[[Category:Oracle]]
[[Category:Oracle SQL]]
[[Category:201201]]