Merge Over Update

Database
Enthusiast

Merge Over Update

Hi,

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 ?

3 REPLIES
Teradata Employee

Re: Merge Over Update

Traditionally, the MERGE is considered to be a better choice than Update & Insert (UPSERT) due to it's strength and flexibility.

UPSERT

The Update and Insert part need to reference: 

  • The same table
  • The same row
  • Should use the PI for a respective operation
  • PI shouldn't be an Identity Column

MERGE

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:

http://forums.teradata.com/forum/database/merge-vs-upsert-0

-Wasif

Enthusiast

Re: Merge Over Update

Thanks Wasif

Rajeev

Teradata Employee

Re: Merge Over Update

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.