CODEWITHSUNDEEP
mysqlsql
anony..nvm
anony..nvm

Reputation: 53

Updating multiple variables with case statement

I have two variables in my_table: updated and status. Each row has a unique id. For a given id='abc123', if the value of 'status' is 'AAA', I want to change status to 'BBB' and 'updated' to NOW(), and leave it unchanged otherwise.

I have an erroneous code:

UPDATE my_table set status=(CASE when status='AAA' THEN 'BBB'), 
SET updated=(CASE when status='AAA' THEN NOW()) where id='abc123';

How do I achieve what I want to do?

Upvotes: 1

Views: 725

Answers (2)

shantanu singh
shantanu singh

Reputation: 1

This would also work.

UPDATE MY_TABLE
    SET STATUS = 'BBB',UPDATED =NOW()
    WHERE ID = 'ABC123' AND STATUS = 'AAA'

Thanks.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

Your syntax is only slightly off, and there is nothing wrong with assigning a column to a CASE expression in an UPDATE statement.

UPDATE my_table
SET
    status = CASE WHEN status = 'AAA' THEN 'BBB' ELSE status END,
    updated = CASE WHEN status = 'AAA' THEN NOW() ELSE updated END
WHERE id = 'abc123';

I added ELSE logic to your CASE expressions which default to not changing the value should the criteria fail. You may remove them, but then non matches would receive NULL values, perhaps not what you intended.

Upvotes: 2

Related Questions