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.