CELKO (1/14/2011)
Since there is a move among MVPs to deprecate this in favor of MERGE and no other product uses it, I would clean up my code and get with the 21-sst Century.
CELKO (1/14/2011)
Jeff, the old stuff I used to post before MERGE did blow up in the proper fashion.UPDATE Orders
SET some_col
= (SELECT item_price
FROM OrderDetails
WHERE OrderDetails.order_nbr = Orders.order_nbr)
WHERE EXISTS
(SELECT *
FROM OrderDetails
WHERE OrderDetails.order_nbr = Orders.order_nbr);
This will of course result in an error, and even the most junior of junior programmers will eventually figure out (probably by asking a senior) what's wrong. At that point, either the query is corrected to match the request, or a note is sent back to management asking for a clarification of the ambiguity in the request.
Since there is a move among MVPs to deprecate this in favor of MERGE and no other product uses it, I would clean up my code and get with the 21-sst Century.
Heh... leave it to you. I was mostly agreeing with you and you turn around and bad mouth me with your "rapier wit". How about you get with the 21st Century and realize you don't need to be a sarcastic steamer to everyone all the time to get your point across? 😉
So far as a "move among MVPs to deprecate this in favor of MERGE", they need to take it one step further to actually do it right if they're going to do it at all. Deprecate INSERT and DELETE as well as UPDATE because MERGE does it all. After all, DELETE in T-SQL also has a FROM clause.
So far as "no other product uses it", so what? I don't believe in the myth of truly portable SQL especially when it comes to writing high performance batch code. 😉 Are we getting closer to true portability becoming a reality? Yes but for true portability, everyone would have to have exactly the same features in their engines which would also stifle the benefits of competition between vendors.
Speaking of "21st century", have you figured out that you don't need to use an archaic push stack to convert Adjacency Lists to Nested Sets, yet?
--Jeff Moden
Change is inevitable... Change for the better is not.