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 the subquery correlated.
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.