Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

UPDATE using a self-join Expand / Collapse
Author
Message
Posted Friday, January 14, 2011 12:40 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, September 28, 2014 1:41 PM
Points: 959, Visits: 2,888
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
Post #1048144
Posted Friday, January 14, 2011 5:52 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 35,770, Visits: 32,436
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1048273
Posted Saturday, January 15, 2011 10:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:50 PM
Points: 7,923, Visits: 9,649
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
Post #1048353
Posted Tuesday, January 18, 2011 5:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:52 AM
Points: 5,672, Visits: 6,482
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1049229
Posted Tuesday, January 18, 2011 7:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #1049316
Posted Saturday, January 29, 2011 1:42 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, September 28, 2014 1:41 PM
Points: 959, Visits: 2,888
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
Post #1055822
Posted Monday, January 31, 2011 4:20 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 6:54 AM
Points: 359, Visits: 244
I'm with Jeff. The update gives me exactly what I would expect.

Post #1056070
Posted Monday, January 31, 2011 10:53 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:24 PM
Points: 1,945, Visits: 3,173
>> 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. <<

Actually, INSERT and UPDATE are single table operations, while MERGE requires a source and target. That is also why the WHERE clause will not be replaced by the ON clause. If you worked with early Sybase, the proprietary "DELETE <target tbl name> FROM <source tbl exp> WHERE.." you would also remember its cardinality problems. When a row matched multiple times in the target table or view, the engine would try to delete it over and over. The results were valid, but the performance sucked.

>> 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. <<

I do believe in portable SQL; I earn a living from it . Most ports are from release(n) to release(n+1) of the same SQL product. The ANSI/ISO Standard features are stable across releases, so the more of them you use, the easier the upgrade. Sometimes getting up to Standards is easy. A bulk text change from ISNULL() to COALESCE() is safe 99.99% of the time. But *= to LEFT OUTER JOIN is harder. A direct translation will work, but not perform. When BIT became a NULL-able numeric data type, you had to do line-by-line inspections and re-writes.

Successful projects start on lesser SQLs (even ACCESS ) then have to move to DB2, Oracle, Teradata or whatever. The ideal was that the project hired good SQL programmers who wrote clean code with minimal proprietary features. I admit that I am one of the few (the only?) programmer I know who puts the ANSI/ISO SQL query in his source code as a comment. One of the nicest e-mails I got was from someone who moved code from *= to infixed join syntax by flipping the comment brackets on a set of VIEWs I had written for them a few years back.

This is the advantage of a high level abstract language. The SQL engine takes the same code and gives me an executable on the newest quantum, Exa-byte, hyper-parallel, agile whiz-bang or on my cell-phone.



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1056310
Posted Tuesday, February 1, 2011 4:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:52 AM
Points: 5,672, Visits: 6,482
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1056731
Posted Tuesday, February 1, 2011 7:47 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:30 AM
Points: 866, Visits: 7,498
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
Post #1056819
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse