17.05 - FROM Clause and UPDATE - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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.