Did you lookup the UPDATE statement in Books Online? It has several
examples:
http://msdn.microsoft.com/library/de...ua-uz_82n9.asp
Do note the warning about the proprietary UPDATE FROM syntax:
"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 (in
other words, if the UPDATE statement is not deterministic)."
That should be common sense but I've seen too many people get tripped
up by this issue. The problem is that this unpredictable behaviour is
silent. No error or warning is given so a serious bug could go
undetected. Check and test your code carefully. The alternative is to
use a correlated subquery:
UPDATE Table1
SET col1 =
(SELECT Table2.col1
FROM Table2
WHERE Table2.col2 = Table1.col2);
I prefer that syntax because it always seems clearer and more logical
to me, also it is standard SQL rather than a Microsoft invention and
finally it doesn't suffer from the bug-feature just described (an error
is reported if the subquery yields more than a single value per row).
Admittedly the proprietary UPDATE FROM version is more concise in some
cases and frequently the proprietary version has the advantage on
performance.
Hope this helps.
--
David Portas
SQL Server MVP
--