Rules for Using Scalar Subqueries in UPDATE Requests
The following rules apply to using scalar subqueries in UPDATE requests:
You can specify scalar subqueries in the FROM and WHERE clauses of an UPDATE request
in the same way as you would for a SELECT request (see “Scalar Subqueries” on page 124 for details). You can only specify a scalar subquery in the FROM clause of an UPDATE
request as an expression within a derived table. You cannot, however, code a derived table as a scalar subquery.
You can specify scalar subqueries in the SET clause of an UPDATE request.
When you specify a correlated scalar subquery in the SET clause, even if the request
has no FROM clause, Teradata Database treats the update as a joined update (see “Example 8: UPDATE With a Scalar Noncorrelated Subquery in its SET Clause” on page 487).
You can specify an UPDATE statement with scalar subqueries in the body of a trigger.
However, Teradata Database processes any noncorrelated scalar subqueries specified
in the FROM, WHERE, or SET clauses of an UPDATE statement in a row trigger as a single‑column
single‑row spool instead of as a parameterized value.