Merge Over Update


Merge Over Update


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 ?

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.


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


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:



Re: Merge Over Update

Thanks Wasif


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.