SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


UPDATE using a self-join


UPDATE using a self-join

Author
Message
tfifield
tfifield
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2475 Visits: 2890
Damn! I thought I'd never say this but score 1 for Joe.

Actually the FROM syntax is very useful as a previous poster mentioned in that the UPDATE can be commented out and replaced with a SELECT to see the count of rows. I can quickly eyeball what's about to be updated. Afte that I usually put a GROUP BY HAVING COUNT(*) > 1 in the select just to make sure there isn't something that I don't know about in the data. Not a perfect solution since it could pop up periodically.
Todd Fifield
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212827 Visits: 41977
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25825 Visits: 12494
CELKO (1/14/2011)
Tom: MERGE gives an error because it has to by definition. Values in a column are scalars; this is part of First Normal Form. I cannot put multiple scalars into that column. An UPDATE has to be deterministic by definition, like any relational operator, so I cannot put in a random value. An UPDATE that has a match has to be completed, so I cannot skip it. The only solutionis to report the truth -- we have a problem, Houston!

Yes, as I said before I'm happy with it giving an error - and incidentally I'd be happy is UPDATE...FROM did the same. Although the Merge syntax has some advantages over UPDATE...FROM it also has a disadvantage - it isn't backwards compatible with SQL 2005 or SQL 2000 and from my experience of trying to get customers to pay for SQL upgrades people will be stuck with maintaining systems using these versions long after the end of standard support.

Ever work with PICK or other NFNF databases? They can keep multiple scalars in a column.

NFNF databases? I once inherited an SQL database that contained an NFNF table! The first thing I did was to make all code that I could control that operated on that table convert anyrelevant rows into a FNF temporary table, do the required manipulations without any NFNF nonsense, and then update affected rows in the original table by converting back to SRF (Standard Rubbish Form). I am only able to account for the existence of NFNF relational databases in the same way as I account for people preferring COM to .NET (ie by attributing it to the indominitable stupidity of developers).

Also, if you played with Sybase, their version of UPDATE. FROM.. was a little different at one time. They made a total out of the duplicate matches and over-wrote the old value with it.

That's awful. Of course I can do that in T-SQL very easily using either "quirky update" (which may require safeguards if the aggregation required is non-commutative or non-associative) or by doing an aggregation using group by, whether I use Merge or Update...From it is extremely straight forward to do. But that it should be done behind the scenes withouut a clear indication that the author of the query required it - that really is awful.
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);


I like that - it's nice and clean.

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.

Deprecating Update...From in favour of Merge would be crazy - the right thing to do is deprecate Update altogether, since it can't do anything Merge can't do. It would probably be a good idea to give it a few years for the development community out there to discover Merge before deprecating Update though.

edit: getting the quote brackets right.

Tom

Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37443 Visits: 9268
siamak.s16 (1/12/2011)
No but he has a book you can buy that proves it!

Joe should just not bother actually making a different post for every thread he posts in and just write "Your code sucks, buy my book to improve it!" for everything. That's pretty much what he does anyway.


Not necessarily true, siamak.s16.

I understand your frustration over responses like this, but if you keep posting comments like this, then people are going to start responding to you the same way that you're responding to him.

We all need a lesson in manners lately, as we've all been guilty of flaming certain people just because we've formed biased opinions about those people's posts. We all need to remember SSC is a professional forum. How we behave here is a reflection of how we act in the workplace. Given how many potential coworkers and employers frequent these forums, it is perhaps the better choice to avoid baiting and respond with facts and questions that allow for professional debates.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57077 Visits: 9730
tfifield (1/14/2011)
Damn! I thought I'd never say this but score 1 for Joe.

Actually the FROM syntax is very useful as a previous poster mentioned in that the UPDATE can be commented out and replaced with a SELECT to see the count of rows. I can quickly eyeball what's about to be updated. Afte that I usually put a GROUP BY HAVING COUNT(*) > 1 in the select just to make sure there isn't something that I don't know about in the data. Not a perfect solution since it could pop up periodically.
Todd Fifield


You can still get more than an idea of how the update will end up looking.

begin transaction;

merge ...
output inserted.*;

commit;



Don't run the commit till you're happy with the data in the output grid.

If you don't want to hold a transaction open, use rollback on the test run, then commit when you're sure it's giving you what you want.

The Output clause has been possible since SQL 2005, and is better than eyeballing a Select version of an Update syntax, since you don't have to change any of the command in order to run it.

Edit: For clarity.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
tfifield
tfifield
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2475 Visits: 2890
Gus,
You busted my chops on this one. I currently only have 1 client on 2008 so I don't actually user the MERGE statement much.

The idea of dumping the output into a table variable that won't be affected by a ROLLBACK is very good. I'm going to use it next time.
Todd Fifield
tertiusdp
tertiusdp
Mr or Mrs. 500
Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)

Group: General Forum Members
Points: 511 Visits: 244
I'm with Jeff. The update gives me exactly what I would expect.
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37443 Visits: 9268
CELKO (1/31/2011)
Actually, INSERT and UPDATE are single table operations, while MERGE requires a source and target.


I haven't moved up to SQL 2k8 yet, so haven't had a chance to use MERGE. But that's good information to know that I didn't know before. Thanks for mentioning it.

CELKO (1/31/2011)

Successful projects start on lesser SQLs (even ACCESS ) then have to move to DB2, Oracle, Teradata or whatever.


Including Access? What other lesser SQLs are there? I'm assuming you mean non-robust solutions, so I'm truly curious as Access (and maybe Lotus) is the only one I know for sure.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Sioban Krzywicki
Sioban Krzywicki
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4410 Visits: 8096
Brandie Tarvin (2/1/2011)
CELKO (1/31/2011)
Actually, INSERT and UPDATE are single table operations, while MERGE requires a source and target.


I haven't moved up to SQL 2k8 yet, so haven't had a chance to use MERGE. But that's good information to know that I didn't know before. Thanks for mentioning it.

CELKO (1/31/2011)

Successful projects start on lesser SQLs (even ACCESS ) then have to move to DB2, Oracle, Teradata or whatever.


Including Access? What other lesser SQLs are there? I'm assuming you mean non-robust solutions, so I'm truly curious as Access (and maybe Lotus) is the only one I know for sure.


Would FoxPro count?

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search