Advantages of "MERGE INTO"

Join Bytes! wrote:[color=blue]
> Hello,
> Is there any advantage of using "MERGE INTO" SQL instead of using
> "EXISTS" AND "NOT EXISTS" in DB2?.
>
> Thanks,
> --Chandra
>[/color]
If all you want to do is an UPDATE then it's a wash in teh ideal case
for MERGE.
If you want to do UPSERT then MERGE is meant to outperform.

MERGE has pretty heavy semantics attached to it. Only if everything is
just right is it a true winner.
Just right means:
ON clause joins on a unique keys and DB2 can figure it out.
If the row merged is a single row, DB2 needs to be able to prove that to
avoid dams between the INSERT and the UPDATE (unique key enforcement).

In FP9 DB2 has gotten quite sophisticated about MERGE and it's used by
SAP in BW and AFAIK R/3

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab