CODEWITHSUNDEEP
mysqlsql
bluethundr
bluethundr

Reputation: 1325

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?

Upvotes: 0

Views: 140

Answers (3)

forpas
forpas

Reputation: 164069

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

Upvotes: 2

Barmar
Barmar

Reputation: 780798

Use a CASE expression to make the value you assign dependent on another column.

UPDATE billing_info_test 
SET Engagement= 
    CASE LinkedAccountId
        WHEN '12345678910' THEN '800000039768'
        WHEN '3542123267612' THEN '800000039768'
    END
WHERE Engagement IS NULL AND LinkedAccountId IN ('12345678910', '3542123267612');

Upvotes: 1

nCessity
nCessity

Reputation: 765

Yes, that statement is not correct (MySQL Reference Manual)

I assume you want to put the same value into the Engagement column of different rows:

Try this:

UPDATE billing_info_test 
  SET Engagement='800000039768' 
  WHERE Engagement IS NULL AND (
    LinkedAccountId = '12345678910' 
    OR LinkedAccountId = '3542123267612'
  );

In the case that you want to put different values into the Engagementcolumn of several rows, you need to issue several independent statements.

Upvotes: 2

Related Questions