Add more conditions to this update case statement

Question

I want to turn this

UPDATE table_name
    SET 
    col_1 = CASE id
        WHEN 123 THEN '333'
        WHEN 124 THEN '334'
    END,
    col_2 = CASE id
        WHEN 123 THEN 'blah1'
        WHEN 124 THEN 'blah2'
    END
WHERE id IN (123,124)

Into this

UPDATE table_name
    SET 
    col_1 = CASE id
        WHEN 123 AND col_3='' AND col_4='' THEN '333'
        WHEN 124 AND col_3='' AND col_4='' THEN '334'
    END,
    col_2 = CASE id
        WHEN 123 AND col_3='' AND col_4='' THEN 'blah1'
        WHEN 124 AND col_3='' AND col_4='' THEN 'blah2'
    END
WHERE id IN (123,124)

Or this

UPDATE table_name
    SET 
    col_1 = CASE id
        WHEN 123 AND col_3<>'' AND col_4='' THEN '333'
        WHEN 124 AND col_3<>'' AND col_4='' THEN '334'
    END,
    col_2 = CASE id
        WHEN 123 AND col_3<>'' AND col_4='' THEN 'blah1'
        WHEN 124 AND col_3<>'' AND col_4='' THEN 'blah2'
    END
WHERE id IN (123,124)

But it's not working.

So for each record, don't update unless certain columns are empty or filled or combination of empty and filled.

Possible duplicate of [MySQL update case help](http://stackoverflow.com/questions/6734231/mysql-update-case-help) — devlin carnate, Jun 14 '16 at 14:21

splash58 · Accepted Answer · 2016-06-14T15:27:50.153

2

Change syntax of case id to case when id=

UPDATE table_name
    SET 
    col_1 = CASE
        WHEN id=123 AND col_3<>'' AND col_4='' THEN '333'
        WHEN id=124 AND col_3<>'' AND col_4='' THEN '334'
    END,
    col_2 = CASE
        WHEN id=123 AND col_3<>'' AND col_4='' THEN 'blah1'
        WHEN id=124 AND col_3<>'' AND col_4='' THEN 'blah2'
    END
WHERE id IN (123,124)

edited Jun 14 '16 at 15:27

answered Jun 14 '16 at 14:17

splash58

26,043
3
22
34

shouldn't that THEN be like this? THEN col_1='333' – leoarce Jun 14 '16 at 14:52
No. `col_1 = CASE` set field name – splash58 Jun 14 '16 at 15:06
you're right, but having hard time with empty. it's not following the empty rules/conditions, and i don't really want to use NULL. is NULL the only way? – leoarce Jun 14 '16 at 15:10
Not clear understood. You want test null instead empty? – splash58 Jun 14 '16 at 15:13
want to test if empty or if not empty. – leoarce Jun 14 '16 at 15:13
If type of the field is a text (char...), `col_4 <> ""` must work – splash58 Jun 14 '16 at 15:15
what about that "as col_2", why doesn't the first one have it? – leoarce Jun 14 '16 at 15:26
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/114655/discussion-between-leoarce-and-splash58). – leoarce Jun 14 '16 at 15:32

Add more conditions to this update case statement

1 Answers1