The upsert form of UPDATE does not support subqueries as WHERE clause conditions. For more information about the upsert form of the UPDATE statement, see UPDATE (Upsert Form).
The syntax rules require that if there are any subqueries in the statement, then any tables referenced in the SELECT must be specified in a FROM clause, either of the subquery itself or of an outer query that contains the subquery.
A correlated subquery references columns of outer tables in an enclosing or containing, outer query. Correlated subqueries provide an implicit loop function within a SQL DML statement.
For details on correlated subqueries and their use, see Correlated Subqueries.
For simple examples of correlated subqueries, see Examples: SELECT Statements with a Correlated Subquery.
Scalar subqueries are another category of subquery that can be coded either as a correlated subquery or as a noncorrelated subquery. A correlated scalar subquery returns a single value for each row of its correlated outer table set. A noncorrelated scalar subquery returns a single value to its containing query. For information about scalar subqueries, see Scalar Subqueries in SELECT Statements.