UPDATE with CASE Statement not updating all Records

  • Does anything appear to be wrong with the following CASE Statement?

    It always updates the First Column correctly but in most instances it does not update the 2nd and 3rd Columns.

    UPDATE Hist

    SET ABCField =

    CASE

    WHEN Field1 = '2012' THEN 'Y'

    ELSE 'N'

    END,

    DEFField =

    CASE

    WHEN Field2 = '9999' THEN 'Y'

    END,

    XyZField =

    CASE

    WHEN Field2 = '9999' THEN 'Y'

    END

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Looks ok to me.

    Please post DDL etc etc... 😀

  • Thanks. I would post the DDL but the DB is of a confidential nature so all that I can do is make something up.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • I don't know if you want this behavior or not, but since your last two cases don't have elses, it will set those fields to null if Field2 does not equal 9999.

  • roryp 96873 (6/28/2012)


    I don't know if you want this behavior or not, but since your last two cases don't have elses, it will set those fields to null if Field2 does not equal 9999.

    and on that note if those 2 columns are null all ready then you would see a null updated to null when field2 is not '9999'


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • Welsh Corgi (6/28/2012)


    Does anything appear to be wrong with the following CASE Statement?

    It always updates the First Column correctly but in most instances it does not update the 2nd and 3rd Columns.

    UPDATE Hist

    SET ABCField =

    CASE

    WHEN Field1 = '2012' THEN 'Y'

    ELSE 'N'

    END,

    DEFField =

    CASE

    WHEN Field2 = '9999' THEN 'Y'

    END,

    XyZField =

    CASE

    WHEN Field2 = '9999' THEN 'Y'

    END

    You don't have ELSE clause for 2nd & 3rd column.

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
    🙂

  • I just checked and none of the CASE Statements are updating all of the records.

    Does anyone have an example of a Sercehed CASE Statement used to perform UPDATES.

    One that works.:hehe:

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Welsh Corgi (6/28/2012)


    I just checked and none of the CASE Statements are updating all of the records.

    Does anyone have an example of a Sercehed CASE Statement used to perform UPDATES.

    One that works.:hehe:

    When you run your update statement, do you get an error, or does it say (x rows(s) affected) after you've run it? It sounds like things are being updated, but they may not being changed the way you are expecting.

  • Welsh Corgi (6/28/2012)


    I just checked and none of the CASE Statements are updating all of the records.

    Does anyone have an example of a Sercehed CASE Statement used to perform UPDATES.

    One that works.:hehe:

    Unless there is an error the update you posted will update ABCField for every row in the table. You said to make it either Y or N and there is no where clause.

    Here is an example of an update with a CASE statement that will update every row in a table and it works. 😛

    UPDATE Hist

    SET ABCField =

    CASE

    WHEN Field1 = '2012' THEN 'Y'

    ELSE 'N'

    END

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Welsh Corgi (6/28/2012)


    I just checked and none of the CASE Statements are updating all of the records.

    Does anyone have an example of a Sercehed CASE Statement used to perform UPDATES.

    One that works.:hehe:

    Well, without a WHERE clause, all rows will be updated: To Y or N on FieldABC, and Y or NULL on DEF & XYZ fields:

    declare @hist table (id int identity(1,1), field1 varchar(10), field2 varchar(10), ABCField varchar(10), DEFField varchar(10), XyZField varchar(10))

    insert into @hist (field1, field2, ABCField, DEFField, XyZField) values

    ('2012', '9999', null, null, null),

    ('2012', '9999', '1', '1', '1'),

    (null, null, '1', '1', '1'),

    ('2002', '1111', '1', '1', '1'),

    (null, null, null, null, null)

    select * from @hist

    UPDATE @Hist

    SET ABCField =

    CASE

    WHEN Field1 = '2012' THEN 'Y'

    ELSE 'N'

    END,

    DEFField =

    CASE

    WHEN Field2 = '9999' THEN 'Y'

    END,

    XyZField =

    CASE

    WHEN Field2 = '9999' THEN 'Y'

    END

    select * from @hist

  • I have almost 200 Columns that I have to update,

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • looks to me like you'll need 200 update statements, one for each possibility: otherwise you end up with unintended updates.

    UPDATE SOMETABLE

    SET ABCField =

    CASE

    WHEN Field1 = '2012' THEN 'Y'

    ELSE 'N'

    END

    WHERE Field1 = '2012'

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Since you can't post the actual table DDL and sample (made up data) for the table that demonstrates your problem, not much we can do unless you can put together a similiar issue using a table data that at least mirrors your problem. Not know exactly what is happening makes it difficult for us to create a situation that mirrors yours.

  • Lowell (6/28/2012)


    looks to me like you'll need 200 update statements, one for each possibility: otherwise you end up with unintended updates.

    I tried that and I encountered something very strange.

    I perform an Update to just update the record to 'Y' if it matched the criteria specified in the WHERE Clause but it updates not Just the record that are specified in the WHERE.

    Then I perform a simple UPDATE WHERE <> and it updates the records with a 'Y' to a 'N'.

    Very strange. I have never seen anything like it.

    I appreciate the feedback from everyone:-).

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • All we are doing is taking shots in the dark. We can't see what you see. You have to set something up that at least mirrors the problem you are having and give that to us.

    We don't have the time to try and figure out what you are doing in an attempt to figure out a solution. Please help us help you.

Viewing 15 posts - 1 through 15 (of 28 total)

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