Conditional Update Statement returning incorrect results on SQL Server 2012

Question

I'm trying to update a table in SQL Server 2012 Management Studio. Rather than writing four separate Update statements, I'm attempting to see if it is possible to write one query that will update the same table for one column in four different ways depending on what the value of a column stores.

Assume I have a table called table_food with the following structure

|Customer|Preference|
+--------+----------+
|John    |McDs      |
|Div     |KFC       |
|Paul    |KFC       |
|Pablo   |Wasabi    |

My idea is to update the Preference column to new values and the query I had written was:

UPDATE table_food
SET Preference = 
    CASE WHEN Preference = 'McDs' Then 'Burger'
         WHEN Preference = 'KFC' Then 'KingsMeal'
    END

Now on my actual table, there are only 8 different options selected for preference and I just need two update 4. (I've just done two as an example above but I have four when statements...so just another two lines)

When I run the query it shows far more rows being affected and checking the results after I notice now there's only one option shown "Burger" with a count of 8 and all the other rows have been set to null. Is there something I'm missing?

David Browne - Microsoft · Accepted Answer · 2019-11-05 23:45:20Z

2

That query will update every row on your table since it lacks a WHERE clause. And a CASE WHEN expression returns NULL if none of the WHEN conditions are true. You might want a query like:

UPDATE table_food
SET Preference = 
    CASE WHEN Preference = 'McDs' Then 'Burger'
         WHEN Preference = 'KFC' Then 'KingsMeal'
    END
WHERE Preference in ('McDs','KFC')

answered Nov 5, 2019 at 23:45

David Browne - Microsoft

90.8k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ed Bangga · Accepted Answer · 2019-11-06 00:44:42Z

2

add else on your case statement.

UPDATE table_food
SET Preference = 
    CASE WHEN Preference = 'McDs' Then 'Burger'
         WHEN Preference = 'KFC' Then 'KingsMeal'
    ELSE Preference 
    END

or if you only want to update affected rows, you do update from

UPDATE table_food
SET Preference = t2.newPreference
FROM
    (SELECT CASE WHEN Preference = 'McDs' Then 'Burger'
                WHEN Preference = 'KFC' Then 'KingsMeal'
                WHEN Preference = 'KFC1' Then 'KingsMeal1'
                WHEN Preference = 'KFC'2 Then 'KingsMeal2'
            END as newPreference, Preference
    FROM table_food) t2
WHERE t2.Preference = table_food.Preference and coalesce(t2.newPreference, '') != ''

edited Nov 6, 2019 at 0:44

answered Nov 6, 2019 at 0:27

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

Collectives™ on Stack Overflow

Conditional Update Statement returning incorrect results on SQL Server 2012

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related