June 22, 2012 at 7:34 am
I would never write that code either, but the value of such an exercise is that someone else might, and in my current position, I write very little T-SQL but I evaluate a LOT of T-SQL. I could easily see someone turn in junk like that and you have to be able to read and understand what the code actually does, versus what the clueless developer thinks it does.
June 22, 2012 at 7:46 am
Put me in the group who'd use CASE.
But Hugo's explanation was definitely worth the trip into the discussion of the question. 🙂
June 22, 2012 at 7:54 am
Koen Verbeeck (6/22/2012)
Very interesting question. I never use this syntax with the correlated subquery, so it would have been nice to give a bit more explanation on that.
+1
Thank you Hugo for the detailed explaination. I don't think I'd ever use syntax like that, but it's good to know how it is actually being evaluated.
June 22, 2012 at 9:11 am
tks for the easy question to round out the week and great discussion! cheers
June 22, 2012 at 9:18 am
sknox (6/22/2012)
Gazareth (6/22/2012)
Nice question, thanks.I'd like to start a quick poll: who's updated the most rows in a live table accidentally by not including a where clause in their update statement?
(Doesn't count if it was within a rollback)
I'll open at 500 😀
Last time I did that, I messed up approximately 2,000 rows :Whistling:. Had to restore that table from backup. Fortunately, that was about 6 years ago.
I think I have a shirt around here somewhere that says 'live dangerously, never use a where clause'. Or something to that effect.
June 22, 2012 at 9:28 am
Hugo Kornelis (6/22/2012)
codebyo (6/22/2012)
Thanks for the question and thanks to Hugo for the detailed explanation.This could be a useful code to set some fields from a a few restricted rows to some value and the rest to NULL in a single UPDATE statement.
I have to disagree. Chances are that you won't be able to understand your code yourself if you revisit it after a few months. For your colleagues, it will be even worse.
As palotaiarpad already commented: a CASE is much better in such a case:
UPDATE YourTableSET SomeColumn = CASE WHEN (....) THEN SomeValue ELSE NULL END
WHERE ....;
Indeed. I had just woken up when I thought about that.
One would have to test what the code does to be able to understand that behaviour.
And using CASE is more straightforward and that's how I've been doing it.
Thank you for the correction. 🙂
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
June 22, 2012 at 9:29 am
Interesting question, John. And great explanation of what's going on under the hood, Hugo!
Thanks to you both.
Rob Schripsema
Propack, Inc.
June 22, 2012 at 9:37 am
Thanks for the question, John, and thank you, Hugo, for the detailed explanation.
I also agree this kind of code should not be used and a CASE statement fits better here.
"El" Jerry.
June 22, 2012 at 9:48 am
sknox (6/22/2012)
Gazareth (6/22/2012)
Nice question, thanks.I'd like to start a quick poll: who's updated the most rows in a live table accidentally by not including a where clause in their update statement?
(Doesn't count if it was within a rollback)
I'll open at 500 😀
Last time I did that, I messed up approximately 2,000 rows :Whistling:. Had to restore that table from backup. Fortunately, that was about 6 years ago.
I had the responsibility of cleaning up for this since I was on the Development team that handled support. One of the Support Techs was manually fixing a patient note for a client (don't ask but it was necessary and there was a standard process until we got a bug fix out). He left off the where clause and every patient note now had the same content. I can't remember how many rows but it was probably many, many thousand. That was a restore from backup to a different location and then copy the data over.
June 22, 2012 at 9:50 am
sknox (6/22/2012)
Gazareth (6/22/2012)
Nice question, thanks.I'd like to start a quick poll: who's updated the most rows in a live table accidentally by not including a where clause in their update statement?
(Doesn't count if it was within a rollback)
I'll open at 500 😀
Last time I did that, I messed up approximately 2,000 rows :Whistling:. Had to restore that table from backup. Fortunately, that was about 6 years ago.
Not myself, but once in a support call with a customer, a few corrupted records needed to be deleted from a table, so I guided him through writing the delete statement, and he executed it before I asked him to!! The where clause had not been written yet so he ended up deleting the whole table!!! To make things worse, it was about 26,000 records of a general ledger table from an accounting system!!!
There was a huge debate between their management and our Technical Support management staffs, until their IT guy calmly entered the conversation and said the database had been backed up right before that happened, so he quickly restored that table and we were back where we started.
That made us TS guys change our procedure and state to our customer "We will run code that may affect your whole accounting system, so don't execute any code until we ask you to do so" before even opening SSMS, and having the customer read the code before confirming him/her to execute it.
"El" Jerry.
June 22, 2012 at 9:52 am
Good question, thanks.
June 22, 2012 at 9:58 am
Syntactically it is correct. Logically it does work. But it might be lacking in clarity for some.
I have used this logic from time to time in ad hoc work building result tables that will eventually be tossed, and have found it easy to use and fast to write. You can however update or in some cases delete needed data quickly if you are not careful, but with care and testing against an appropriate collection of contrived data, you can make it happen.
Whether you should or not is up to you. As stated before just because you can do it does not mean that you should do it.
Not all gray hairs are Dinosaurs!
June 23, 2012 at 10:38 pm
A really nice and interesting questions.
Thanks. 🙂
June 25, 2012 at 3:29 am
Some nice stories, cheers 🙂
Had someone at a place I worked, "sold" about 100,000 cars in milliseconds. :pinch:
June 25, 2012 at 4:47 am
Understanding the query was the key here
Thanks Hugo for the explanation.
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply