|
|
Posted by Erland Sommarskog on 07/20/05 22:22
(wackyphill@yahoo.com) writes:
> Awesome Erland, thanks that's what I was looking for!
>
> I've never seen a FROM / JOIN on an update statement before. I assume
> the JOIN insures that only the rows in the table that match the rows in
> the inserted table are modified rather than the whole table?
>
Welcome to SQL Server! :-)
FROM in UPDATE and DELETE is an extension to SQL Server (both Microsoft
and Sybase) which is very, very useful. This makes an UPDATE or DELETE
to be just like a SELECT. Thus:
UPDATE tbl
SET datecol = convert(char(8), t.datecol, 112)
FROM tbl t
JOIN inserted i ON t.keycol1 = i.keycol1
AND t.keycol2 = i.keycol2
...
Is just like:
SELECT datecol = convert(char(8), t.datecol, 112)
FROM tbl t
JOIN inserted i ON t.keycol1 = i.keycol1
AND t.keycol2 = i.keycol2
...
There are two drawbacks with this syntax:
1) It is not portable. If you want your code to run on other DBMS's
as well, don't use it.
2) If your joins are incomplete, so that the corresponding SELECT would
have duplicate rows, the result of the UPDATE statement is
unpredictable.
On the other hand, say that you have something like:
UPDATE tbl
SET col = (SELECT SUM(b.othercol)
FROM othertbl b
WHERE tbl.keycol = b.keycol)
If you rewrite this in the proprietary syntax:
UPDATE tbl
SET col = b.othercolsum
FROM tbl a
JOIN (SELECT keycol, othercolsum = SUM(othercol)
FROM othertbl
GROUP BY keycol) AS b ON a.keycol = b.keycol
My experience is that the last performs considerably better than the
ANSI-compliant query above. This is even clearer when you need to set
more than one column.
> What's the FROM clause for though? Is it required? Or was it just so
> you could alias the table to save typing?
Yes, FROM is needed in this syntax, but you don't actually have to
repeat the target table. This is legal:
UPDATE tbl
SET datecol = convert(char(8), tbl.datecol, 112)
FROM inserted i
WHERE tbl.keycol1 = i.keycol1
AND tbl.keycol2 = i.keycol2
...
But I don't like this, as it's not the same as a SELECT. By the way, if
I use a correlated subquery for the SET clause, I still put in a FROM
just to be able to use an alias.
Finally, I should admit that using EXISTS (SELECT * FROM inserted...) in
this case is cleaner, but writing joins requires less thinking. :-)
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
[Back to original message]
|