Merge or Upsert statement in Teradata with examples

Merge Statement in Teradata

Merge statement is also referred as Upsert that

  • inserts a record to a table in a database if the record doesn’t exist.
  • if the record already exists , update the existing record.

It combines the UPDATE and INSERT statements into a single statement with two conditional test clauses.


We can also delete the rows if the record matched by specifying : WHEN MATCHED,DELETE. The source and target table should have the same PI and PPI that helps to process the merge statement faster in Teradata.


It updates the matched target table row with the values taken from the source table row. Also it deletes the matching target table row.


It inserts the current source row into the target table.

Syntax of Merge Statement in Teradata

Example for Merge Statement in Teradata

Lets merge the Employee and Employee_bkup table using Merge statement in Teradata. Here the Employee_bkup table is the target table and Employee table is the source for that. We are using merge statement in this example to load the new employees and update the existing employees in the backup table,

Target table – Employee_Bkup

Merge statement example in Teradata
Empoyee_Bkup table as Target

Source table – Employee

Merge Statement example in Teradata
Employee table as Source

Merge or Upsert query in Teradata

If the employee id of target and source table is matched, the values will be updated in the Employee_Bkup table. If the condition is not matched, the new employee details will be inserted into the Employee_Bkup table.

Output of the Merge statement in Employee_Bkup Table

Merge statement output in Teradata
Merge statement output in Teradata

The Department id is updated to 6745 in the Employee_Bkup table as employee id 123 is already present in the table. Similarly the new employee ids 121 & 122 are inserted into the backup table.

Common errors in the Merge statement query

If we specify the Alias name of the target table in the update statement, it will fail the merge statement as below. We need to remove T. in the query to avoid this error

Also if we not specify all the primary or partitioned columns in the ON condition of the merge statement, it will failed the merge statement as below.

Recommended Articles