August 2, 2019 at 12:00 am
Comments posted to this topic are about the item SQL Update Statement Awareness
Owen White
August 2, 2019 at 2:26 am
Love this question! but as always someone always beats me to the answer...
August 2, 2019 at 7:09 am
Nice, easy one to end the week on, thanks Owen
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
August 2, 2019 at 8:11 am
Using SQL Server 2017 Dev edition, the answer provided results in error "An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'."
For this answer to work it would have to be modified to a valid simple CASE expression:
UPDATE my_table SET my_field = CASE my_field WHEN 1 THEN 10 WHEN 2 THEN 20 ELSE my_field END;
Otherwise nothing is updated and values remain 1,2,3.
August 2, 2019 at 8:26 am
Quite. The UPDATE statement is syntactically incorrect in any version of SQL Server and will produce an error. Therefore the second statement will return the original values.
Either the question or the answer needs correcting.
August 2, 2019 at 11:22 am
I'm surprised more people didn't choose 1,2,3. The update will fail, leaving the 2nd select giving the original values.
August 2, 2019 at 11:27 am
Using SQL Server 2017 Dev edition, the answer provided results in error "An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'."
For this answer to work it would have to be modified to a valid simple CASE expression:
UPDATE my_table SET my_field = CASE my_field WHEN 1 THEN 10 WHEN 2 THEN 20 ELSE my_field END;
Otherwise nothing is updated and values remain 1,2,3.
the Update statement aught to have shown:
UPDATE my_table SET my_field = CASE my_field WHEN 1 THEN 10 WHEN 2 THEN 20 END;
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
August 2, 2019 at 11:30 am
The correct answer is 1, 2, 3 because the statement provided is invalid, so the table should not change.
August 2, 2019 at 12:05 pm
I got the "right" answer because I recognized the concept of the question - that if we do not supply an ELSE in a CASE statement, anything that fails the WHEN conditions results in NULL.
And I totally missed the fact that the CASE statement wasn't formed properly so the UPDATE wouldn't work.
Who promoted this code to PROD without testing? 🙂
August 2, 2019 at 12:31 pm
Alternatively, put a WHERE clause on the statement so it won't have unnecessary IO of updating rows that don't need to change.
August 2, 2019 at 1:44 pm
I got the "right" answer because I recognized the concept of the question - that if we do not supply an ELSE in a CASE statement, anything that fails the WHEN conditions results in NULL.
And I totally missed the fact that the CASE statement wasn't formed properly so the UPDATE wouldn't work.
Who promoted this code to PROD without testing? 🙂
I also got the correct answer as I "ran it in my head" and recognised what the question was asking rather that the syntax error in the case statement, I see a few of these on QotD and try to answer the spirit of the question rather than allow my natural pedantry to take the lead.
August 2, 2019 at 2:06 pm
I'm surprised more people didn't choose 1,2,3. The update will fail, leaving the 2nd select giving the original values.
Exactly my thought. I can't be expected to interpret the intent of the question. When you show me an invalid case statement, I'm going to pick the CORRECT answer, which was 1, 2, 3.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
August 2, 2019 at 2:25 pm
Very instructive - and scary - answer!
I guess there is or was a typo in the SQL syntax. Definitely worth calling attention to any such error, but the question is an extremely valuable warning.
The fact that the unhandled values get set to NULL is horrifying, and now I realize I need to practice variations of CASE in an update to make sure I always get the expected results.
Thanks!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
August 2, 2019 at 2:31 pm
First time ever clicked on the link for an awareness question. I read the question and my instant thought was that doesn't look right how is the case going to work.
Next thought, well maybe I am not "aware" let's try it.
Next thought, ah Mr White was trying to catch me up, and I chose 1,2,3.
Next thought after a big red X you are wrong came up, ah attention to detail is still as important as it has always been.
Next thought is programming of the answer wrong? or the syntax in the question?
From my test I saw the edge case of the null for value 3
Thanks for the awareness.
August 2, 2019 at 2:48 pm
Yes update statement is not coded correctly so then you have to infer what the intent of the question is. It is far too common on the QOTD.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply