17.05 - Rules for Using Scalar Subqueries in UPDATE Requests - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Advanced SQL Engine
Teradata Database
Release Number
January 2021
English (United States)
Last Update
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. 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, the database treats the update as a joined update. See Example: UPDATE With a Noncorrelated Subquery in its WHERE Clause.
  • You can specify an UPDATE statement with scalar subqueries in the body of a trigger.

    However, the 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.