Merge or Upsert statement in Teradata with examples
Contents
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.
- WHEN MATCHED, UPDATE.
- WHEN NOT MATCHED, INSERT.
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.
WHEN MATCHED:
It updates the matched target table row with the values taken from the source table row. Also it deletes the matching target table row.
WHEN NOT MATCHED:
It inserts the current source row into the target table.
Syntax of Merge Statement in Teradata
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
MERGE INTO <Target_Table> USING <Source_Table> ON <Match_Condition> WHEN MATCHED THEN UPDATE SET <Target_Table_Column> = <Source_Table_Column> WHEN NOT MATCHED THEN INSERT VALUES (<Source_Table_Column_1>,<Source_Table_Column_2>,.......); |
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
Source table – Employee
Merge or Upsert query in Teradata
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
MERGE INTO BANKING_DB.EMPLOYEE_BKUP as T USING BANKING_DB.EMPLOYEE as S ON T.Employee_Id = S.Employee_Id WHEN MATCHED THEN UPDATE SET Department_Id = S.Department_Id, Employee_Name = S.Employee_Name, Join_Date = S.Join_Date, Mobile_Number = S.Mobile_Number WHEN NOT MATCHED THEN INSERT VALUES (S.Employee_Id,S.Department_Id,S.Employee_Name,S.Join_Date,S.Mobile_Number); |
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
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
|
1 2 3 4 5 6 7 8 |
THEN UPDATE SET T.Department_Id = S.Department_Id, T.Employee_Name = S.Employee_Name, T.Join_Date = S.Join_Date, T.Mobile_Number = S.Mobile_Number MERGE Failed 3810: Column/Parameter table.A.A does not exist |
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.
|
1 2 3 4 |
ON T.Employee_Id = S.Employee_Id The search condition must fully specify the Target table primary index and partition column(s) and expression must match INSERT specification primary index and partition column(s). |
Recommended Articles
