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.