UPDATE FROM is dead … or it should be. Hugo Kornelis has done a good job of explaining why.
Apart from a badly thought out and non-portable syntax, UPDATE FROM suffers a notorious bug/feature. I say “notorious” but it’s probably not notorious enough because it is still common to see people get tripped up by it. If I write:
UPDATE Alpha
SET z = Beta.z
FROM Alpha
JOIN Beta
ON Alpha.x = Beta.x ;
and if Beta.x is not unique then Alpha.z will get updated with some random value from one of the matching rows in Beta.z. This is documented in Books Online as follows:
“The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic.”
MERGE handles the same situation more wisely and safely: it returns a runtime error if the join is non-deterministic:
MERGE INTO Alpha
USING Beta
ON Alpha.x = Beta.x
WHEN MATCHED THEN UPDATE
SET z = Beta.z ;
Msg 8672, Level 16, State 1, Line 42
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
MERGE is also standard SQL, works in other DBMSs and frequently results in more efficient execution plans than UPDATE FROM.
UPDATE FROM isn’t originally Microsoft’s fault because it’s a legacy of SQL Server’s ancient historical roots at Sybase. But it is Microsoft’s fault they haven’t deprecated it yet. You can vote for deprecating UPDATE FROM on Connect:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=332437