Posted by Erland Sommarskog on 09/01/07 10:09

Bart op de grote markt (warnezb@googlemail.com) writes:
> But well I learned from your post that you put a FROM-clause in your
> update. I did not know that you could do that. Unfortunately I can
> only test it on monday again... Thx for your input!

I should point out that this is syntax that is proprietary to SQL Server
and Sybase (and I think Informix has it too). In fact, you can also do:


UPDATE tbl
SET col = ...
FROM tbl
JOIN othertbl ...

just like in a regular SELECT. The one thing to watch out for is that if
your join conditions are such that they include rows from the target
table multiple times, it is unpredictable which value that will win.

While frowned at by purists, it's a very convenient extension, as it
makes your UPDATE (and DELETE) statements easier to write. Also, it is
my experience that an UPDATE with FROM JOIN performs better than using
subqueries in the SET clause. This is particularly evident if you need
to retrieve several columns from the same table. With the ANSI syntax
you need to repeat the subquery each time. (Since SQL Server does not
support set constrctors).



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]