Update columns in MySQL depending on cell value

I want to update multiple cells in a MySQL table depending on value.

I find that if I take a field called engagement I can update it one at a time like so:

UPDATE billing_info_test 
SET Engagement='800000039768' 
WHERE Engagement IS NULL and LinkedAccountId = '12345678910'

However, if I try to do multiple at a time it fails and I get an error:

UPDATE billing_info_test 
SET Engagement='800000039768' 
WHERE Engagement IS NULL and LinkedAccountId = '12345678910' 
AND SET Engagement='800000039768' 
WHERE Engagement IS NULL and LinkedAccountId = '3542123267612';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘SET Engagement=’800000039768′ WHERE Engagement IS NULL and LinkedAccountId = ’30’ at line 1

Is it incorrect to use AND between these statements? What am I doing wrong?

Answer

You can do it with a CASE statement:

UPDATE billing_info_test 
SET 
  Engagement = CASE 
    WHEN LinkedAccountId = '12345678910' THEN 'something'
    WHEN LinkedAccountId = '3542123267612' THEN 'somethingelse'
    WHEN LinkedAccountId IN ('354', '123', '000') THEN 'somethingelsetoo'
    ELSE 'default'
  END
WHERE Engagement IS NULL

Advertisement

Answer