SQL Update Statement Awareness

  • ..

  • USE TestDB;
    GO

    --Weird question, pleas try this in Results to Text mode:

    CREATE TABLE dbo.my_table (my_field INT);
    INSERT dbo.my_table VALUES (1),(2),(3),(30),(40),(50);
    GO
    -- Syntax error in CASE clause!
    UPDATE dbo.my_table SET my_field = CASE WHEN 1 THEN 10 WHEN 2 THEN 20 END;
    GO
    /*
    Results
    (6 row(s) affected)
    Msg 4145, Level 15, State 1, Line 10
    An expression of non-boolean type specified in a context where a condition is expected,
    near 'THEN'.
    */
    --If you correct your CASE syntax error to:
    UPDATE dbo.my_table SET my_field = CASE my_field WHEN 1 THEN 10 WHEN 2 THEN 20 END;
    --then incorrectly used CASE will destroy your data with result set Null, 10, 20.
    SELECT DISTINCT my_field FROM dbo.my_table;
    GO
    /*
    (6 row(s) affected)
    Results
    my_field
    -----------
    NULL
    10
    20

    (3 row(s) affected)
    */

    --Reset my_table values:
    TRUNCATE TABLE dbo.my_table;
    INSERT dbo.my_table VALUES (1),(2),(3),(30),(40),(50);
    GO
    /*
    Results
    (6 row(s) affected)
    */

    --The correct used clause CASE for your task is:
    UPDATE dbo.my_table SET my_field = CASE my_field
    WHEN 1 THEN 10
    WHEN 2 THEN 20
    ELSE my_field
    END;
    SELECT DISTINCT my_field FROM dbo.my_table;
    GO
    /*
    Results
    (6 row(s) affected)

    (6 row(s) affected)
    my_field
    -----------
    3
    10
    20
    30
    40
    50

    (6 row(s) affected)
    */

    DROP TABLE dbo.my_table;
    --If you used NOT NULL in the column definition my_field, then incorrectly used CASE
    --could not destroy your data:
    CREATE TABLE dbo.my_table (my_field INT NOT NULL);
    INSERT dbo.my_table VALUES (1),(2),(3),(30),(40),(50);
    UPDATE dbo.my_table SET my_field = CASE my_field WHEN 1 THEN 10 WHEN 2 THEN 20 END;
    SELECT DISTINCT my_field FROM dbo.my_table;
    GO
    /*
    Results
    (6 row(s) affected)
    Msg 515, Level 16, State 2, Line 67
    Cannot insert the value NULL into column 'my_field', table 'TestDB.dbo.my_table';
    column does not allow nulls. UPDATE fails.
    The statement has been terminated.
    my_field
    -----------
    1
    2
    3
    30
    40
    50

    (6 row(s) affected)
    */
    DROP TABLE dbo.my_table;
    GO

    • This reply was modified 5 years, 3 months ago by  George Vobr.
  • To all,  I apologize for the bad example in the CASE Statement and it was not a trick question.  Yes, it should have been the following:

    CASE my_field ...

    This was my first post and I thought that SQL Server Central would review the question before posting.  I did not even think it would get approved.  I just found out it was published.

    I ran across this scenario as I was trying to quickly update some data with unintentional outcome.  Good thing I had a backup and found the issue right away after running the script.  I thought this could be a reminder to all that a simple straight forward query can have bad consequences to your data which points to the importance of testing pre and post.

    I hope that this helps someone in the future!

    Owen

    Owen White

  • Thank you!  That was my mistake.  I thought the QoD would get reviewed before they published it.

    Owen White

  • The Syntax looked wrong based on what I knew but then I don't know everything so thought I'm going to learn another Microsoft shortcut. I actually like the incorrect Syntax. Too bad.

  • I understand Owen's intent, but the BIG lesson here is that you should think twice when running an update statement without a where clause. 🙂

    5ilverFox
    Consulting DBA / Developer
    South Africa

  • Owen White wrote:

    To all,  I apologize for the bad example in the CASE Statement and it was not a trick question.  Yes, it should have been the following:

    CASE my_field ...

    This was my first post and I thought that SQL Server Central would review the question before posting.  I did not even think it would get approved.  I just found out it was published.

    I ran across this scenario as I was trying to quickly update some data with unintentional outcome.  Good thing I had a backup and found the issue right away after running the script.  I thought this could be a reminder to all that a simple straight forward query can have bad consequences to your data which points to the importance of testing pre and post.

    I hope that this helps someone in the future!

    Owen

    Thanks a lot for coming back and posting all of this. Good lesson about updates and much respect for coming back and explaining the problems with the question.

    Sue

  • srienstr wrote:

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

    This is a really important point.  If you are updating a number of columns, and want to execute only a single UPDATE statement (to minimize I/O), then using the ELSE to pass through the existing value is reasonable.  But if you are only updating a single column, definitely use a WHERE clause to ensure you only update the record if the data is chaning.  UPDATEs that don't change data still result in I/O (both for the tran log and the data page), cause triggers to fire (which can be useful in some circumstances), result in TRUE from UPDATE(column), etc.

Viewing 8 posts - 16 through 22 (of 22 total)

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