Post reply

SQL Update Statement Awareness

  • August 2, 2019 at 12:00 am

    #3660884

    Comments posted to this topic are about the item SQL Update Statement Awareness

    Owen White

  • August 2, 2019 at 2:26 am

    #3668099

    Love this question! but as always someone always beats me to the answer...

  • August 2, 2019 at 7:09 am

    #3668145

    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

    #3668153

    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

    #3668154

    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

    #3668200

    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

    #3668201

    JimHalliday wrote:

    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

    #3668206

    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

    #3668213

    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

    #3668216

    Alternatively, put a WHERE clause on the statement so it won't have unnecessary IO of updating rows that don't need to change.

    Puto me cogitare, ergo puto me esse.
    I think that I think, therefore I think that I am.

  • August 2, 2019 at 1:44 pm

    #3668259

    GaryV wrote:

    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.

    • This reply was modified 4 years, 8 months ago by  carl.eaves.

  • August 2, 2019 at 2:06 pm

    #3668275

    sequelgarrett wrote:

    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

    #3668283

    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

    #3668288

    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

    #3668293

    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