What are the advantages of using Merge ? and when do we prefer using MERGE over the UPDATE
Does using a merge give better performance every time ?
Traditionally, the MERGE is considered to be a better choice than Update & Insert (UPSERT) due to it's strength and flexibility.
The Update and Insert part need to reference:
The Insert portion may specify different partitions
The Update can modify partitioning fields
Partitioning fields need not be specified in WHERE of Update portion
For additional details, please refer:
MERGE and UPDATE perform two different tasks. UPDATE only updates existing rows. MERGE tests to see if the row exists, performs an INSERT if not and performs and UPDATE if the row exists. If only UPDATEs are to be performed, the UPDATE should be used. If the additional functionality is required, then MERgE is a much better and much better performaing answer than two passes, one to do the UPDATEs and one to do the INSERTS.