Introduction
This code snippet is regarding updating a teradata table by
selecting relevant records from another table. The similar job in oracle is
quite straight forward and does not require any easy explanation. But for
someone who has started as a beginner in teradata, the job becomes a bit
tougher as there is no official documentation available representing the
syntax.
Requirements
Any version of Teradata database
Problem Description
The table Update_name needs to be updated through names by
selecting corresponding ID from table original_profile. The status of source
and target tables is given below.
Figure 1 - Source table
Figure 2 - Target table
Code
The solution to the above problem is stated below.
Listing 1
UPDATE a
FROM Update_name a,(SELECT distinct ID, NAME FROM original_profile) b
SET NAME = b.NAME
WHERE a.ID=b.ID;
Output
Listing 2
UPDATE a FROM Update_name a,(SELECT distinct ID,NAME FROM original_profile) b
SET NAME = b.NAME WHERE a.ID=b.ID;
*** Update completed. 5 rows changed.
*** Total elapsed time was 2 seconds.
Figure 3 - Target table after updating
Explanation
The update statement is self-explanatory. But care should be
taken while selecting data from the source table. Here, only distinct records
have been selected to in order to avoid selecting duplicate rows from the source
table, which may create the following error while updating.
“*** Failure 7547 Target row updated by
multiple source rows.”
Conclusion
By following the format provided above, any table can be
updated through records being selected from another table. Complex selection
and update criteria can be used as required by modifying the above update
statement accordingly.
