CODEWITHSUNDEEP
mysql
Garrett R
Garrett R

Reputation: 531

Mysql CASE and UPDATE

I'm trying to do this query which updates only the first column that is empty. Here is a query so far: 

UPDATE `names` SET 
`name_1` = CASE WHEN `name_1` = '' then 'Jimmy' else `name_1` end,
`name_2` = CASE WHEN `name_1` != '' and `name_2` = '' then 'Jimmy' else `name_2` end

It updates all of columns with 'Jimmy'. I think that that's because the SET will update it then move on to the next SET and will update that etc...Am I right on what's causing this? If so how could I fix this? If not how should I rewrite this?

Upvotes: 0

Views: 106

Answers (2)

Tom
Tom

Reputation: 6663

I think if you swap the order, it will work properly.

Try this:

UPDATE `names` SET 
`name_2` = CASE WHEN `name_1` != '' and `name_2` = '' then 'Jimmy' else `name_2` end,
`name_1` = CASE WHEN `name_1` = '' then 'Jimmy' else `name_1` end

Upvotes: 1

Tom
Tom

Reputation: 6663

I'm wondering if null values might be causing you problems. You could use the IFNULL function to convert them to empty strings (in case you have empty strings and NULLs). Try this:

UPDATE `names` SET 
    `name_1` = CASE WHEN IFNULL(`name_1`, '') = '' then 'Jimmy' else `name_1` end,
    `name_2` = CASE WHEN IFNULL(`name_1`, '') != '' and IFNULL(`name_2`, '') = '' then 'Jimmy' else `name_2` end

or if you have all nulls:

UPDATE `names` SET 
    `name_1` = CASE WHEN `name_1` IS NULL then 'Jimmy' else `name_1` end,
    `name_2` = CASE WHEN `name_1` IS NOT NULL and `name_2` IS NULL then 'Jimmy' else `name_2` end

Upvotes: 0

Related Questions