leoarce leoarce - 5 months ago 9
MySQL Question

Add more conditions to this update case statement

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.

Answer

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)