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 as you do in a SELECT request. See Scalar Subqueries in SELECT Statements. 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 the 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.