Teradata - Update with Joins to Another Table

access_time 2 months ago visibility10 comment 0

In SQL Server, JOIN can be used to update target table with values from a joined table. In Teradata, this syntax is not as simple or intuitive as SQL Server. 

Update with a JOIN

The following example updates table test_table1 with data from test_table2.

Database TestDb;
UPDATE test_table1
FROM test_table2 AS SRC
SET amount = amount * SRC.multiplier
WHERE test_table1.category = SRC.category
AND test_table1.amount > 100
AND SRC.is_deleted = 0;

Update with a corelated subquery

This examples shows how to update a table with a corelated subquery.

Database TestDb;
UPDATE test_table1 AS TGT
SET amount = (SELECT amount FROM test_table2 AS SRC WHERE TGT.category = SRC.category)
WHERE TGT.amount > 100;
info Last modified by Raymond 2 months ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn or Twitter.

Want to publish your article on Kontext?

Learn more

More from Kontext

visibility 26
thumb_up 0
access_time 28 days ago

In my article Connect to Teradata database through Python , I demonstrated about how to use Teradata python package or Teradata ODBC driver to connect to Teradata. In this article, I’m going to show you how to connect to Teradata through JDBC drivers so that you can load data directly into Spark ...

visibility 284
thumb_up 1
access_time 5 months ago

This page shows how to create Hive tables with storage file format as Parquet, Orc and Avro via Hive SQL (HQL). The following examples show you how to create managed tables and similar syntax can be applied to create external tables if Parquet, Orc or Avro format already exist in HDFS. Example ...

visibility 1714
thumb_up 0
access_time 10 months ago

OREPLACE functions in Teradata can be used to replace or remove characters from a string. OREPACE is Teradata's extension to ASNI SQL. The usual REPLACE function is not available. REPLACE function is commonly implemented in many other SQL databases such as SQL Server, MySQL, BigQuery, Oracle, etc.