CodeSnip: How to Update a Table by Selecting Records from another Table
page 1 of 1
Published: 29 Dec 2008
Abstract
In Oracle, the primary focus of this article is not difficult, but in teradata, the same job becomes tougher as the syntax itself varies quite a bit from Oracle and is not a part of the official documentation. In this code snippet, Deepakar explains how to deal with the stated problem with the help of relevant syntax. He begins with a detailed description of the problem followed by relevant code and its analysis.
by Deepankar Sarangi
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 23532/ 70

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.



User Comments

Title: Really Helpful   
Name: Auronica
Date: 2010-06-09 11:38:30 PM
Comment:
Thank you for providing the detailed information and this is the question mostly asked in interviews.
Title: Very Helpful   
Name: Lucky
Date: 2010-06-02 10:10:23 AM
Comment:
Thanks a lot for the information. This explained exactly ehat I was looking for.
Title: good and simple article   
Name: Kumar
Date: 2009-09-14 1:56:04 PM
Comment:
Its very simple and crystal clear article. Thanks
Title: nice article   
Name: manan shah
Date: 2009-01-07 11:39:28 PM
Comment:
this is nice article.nice for learning.........

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2017 ASPAlliance.com  |  Page Processed at 2017-07-28 2:37:22 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search