SQL Update Statement Awareness

  • Owen White

    Ten Centuries

    Points: 1224

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

    Owen White

  • SQLSharma

    Say Hey Kid

    Points: 676

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

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71320

    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”

  • JimHalliday

    Grasshopper

    Points: 19

    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.

  • 16stonepig

    SSC Veteran

    Points: 224

    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.

  • sequelgarrett

    Right there with Babe

    Points: 778

    I'm surprised more people didn't choose 1,2,3. The update will fail, leaving the 2nd select giving the original values.

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71320

    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”

  • William Vach

    SSCarpal Tunnel

    Points: 4728

    The correct answer is 1, 2, 3 because the statement provided is invalid, so the table should not change.

  • gvoshol 73146

    Hall of Fame

    Points: 3092

    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?  🙂

     

  • srienstr

    SSCrazy

    Points: 2240

    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.

  • carl.eaves

    SSC-Addicted

    Points: 451

    gvoshol 73146 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 3 weeks ago by  carl.eaves.
  • below86

    SSChampion

    Points: 11234

    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

  • webrunner

    One Orange Chip

    Points: 29866

    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 says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • bill.soetebier

    Grasshopper

    Points: 13

    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.

     

     

     

  • rustman

    Hall of Fame

    Points: 3906

    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 23 total)

You must be logged in to reply to this topic. Login to reply