CASE statement updates all my values to NULL when there is no matches?

Question

I'm having an issue using the CASE statement in MySql. I added some code below as an example of what I'm trying to do. I thought that if there are no matches in the WHEN statement that no changes will occur, but that doesn't seem to happen.

I don't have a record that has a value 66 for contact_id in the my_contacts table, so I figured nothing will happen, but instead all the values in the zip_code column change to null. Why is this?

UPDATE my_contacts
SET zip_code =
CASE
 WHEN contact_id = 66 THEN '33333'
END;

How Do i only update a few records using case? For instance, I want to update only records that match contact_id = 1 and contact_id =2. It does update those two records, but also changes an existing zip code from '90004' to NULL, Why is that?

UPDATE my_contacts 
SET zip_code = 
CASE 
WHEN contact_id = 1 THEN '94301' 
WHEN contact_id = 2 THEN '08540' 
END;

Query OK, 3 rows affected (0.01 sec) Rows matched: 5 Changed: 3 Warnings: 0

I rolled back your last edit because it changed the original meaning of the question - please avoid doing this. See my updated answer for the second question. — Paul Bellora, Sep 26 '13 at 02:11

score 5 · Answer 1 · edited May 23 '17 at 12:26

You're using CASE incorrectly. Use a WHERE clause for a conditional update:

UPDATE my_contacts SET zip_code = '33333' WHERE contact_id = 66;

A CASE expression merely decides what value to return - not which rows are affected. From the documentation:

If there [is] no matching result value, the result after ELSE is returned, or NULL if there is no ELSE part.

So what you're doing is equivalent to this:

UPDATE my_contacts
SET zip_code = CASE WHEN contact_id = 66 THEN '33333' ELSE NULL END
;

In response to your update, you would need to do the following:

UPDATE my_contacts 
SET zip_code =
    CASE 
        WHEN contact_id = 1 THEN '94301' 
        WHEN contact_id = 2 THEN '08540' 
    END
WHERE contact_id IN (1, 2)
;

But it only makes sense to do this for a few records. See this post for more scaleable solutions: MySQL bulk INSERT or UPDATE

score 3 · Answer 2 · answered Feb 09 '16 at 15:50

3

UPDATE my_contacts 
 SET zip_code = 
    CASE  
        WHEN contact_id =  1  THEN  '94301'  
        WHEN contact_id =  2  THEN  '08540' 
    ELSE zip_code
    END 
;

answered Feb 09 '16 at 15:50

Floyd

39
1

This solution worked for me. It may not be as performant as using the `WHERE` clause from Paul's answer because it will hit more records than necessary. However, for a switch/case statement in general across multiple languages, it may be good practice to _always_ include the **default** fallback (or in this case `ELSE`) statement. So a combination using `WHERE` in addition to `ELSE` would be a better approach. – chemturion Nov 08 '19 at 23:15

CASE statement updates all my values to NULL when there is no matches?

2 Answers2