The use of the from clause to perform joins in update and delete statements is a Transact-SQL extension to ANSI SQL. Subqueries in ANSI SQL form can be used in place of joins for some updates and deletes.
This example uses the from syntax to perform a join:
update t1 set t1.c1 = t1.c1 + 50 from t1, t2 where t1.c1 = t2.c1 and t2.c2 = 1
The following example shows the equivalent update using a subquery:
update t1 set c1 = c1 + 50
where t1.c1 in (select t2.c1
from t2
where t2.c2 = 1)
The update mode that is used for the join query depends on whether the updated table is the outermost query in the join order—if it is not the outermost table, the update is performed in deferred mode. The update that uses a subquery is always performed as a direct, deferred_varcol, or deferred_index update.
For a query that uses the from syntax and performs a deferred update due to the join order, use showplan and statistics io to determine whether rewriting the query using a subquery can improve performance. Not all queries using from can be rewritten to use subqueries.
