FROM Clause and UPDATE - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

The optional FROM list included in the UPDATE syntax is a Teradata extension to support correlated subqueries and derived tables in the search conditions for UPDATE.

Specify the FROM clause for the following reasons only:
  • To provide the outer scope of a table with columns referenced in a subquery, making the subquery a correlated subquery.
  • To permit references to a derived table.
  • To specify joined tables for an update operation.

If a table is listed in the FROM clause for the UPDATE and not in the FROM clause for a subquery, then field references in the subquery are scoped at the outer level, making it a correlated subquery.

The following rules apply to the use of correlated subqueries in the FROM clause of an UPDATE request:
  • If a FROM clause is specified for the UPDATE syntax you are using, then any correlation name used must be specified in the FROM clause.
  • If a correlation name is specified for the updated table name in the FROM clause, this correlation name, rather than the original name, must be used as the table_name that follows the UPDATE keyword. This FROM clause is optional if no joined tables are specified for an UPDATE.
  • If an inner query column specification references an outer FROM clause table, then the column reference must be fully qualified.
  • If the FROM clause is omitted, you can specify a correlation name for the table_name that follows the UPDATE keyword.

Also see Correlated Subqueries.