MYSQL Update same column with multiple where clause

I found this question, seems like same as mine. However, there’s a little difference between the two as that one is using the same column in where clause and I have a different one.

This is what I’m trying to achieve;

UPDATE table 
SET recent = 0 WHERE recent = 1 //update all `recent` column where its value is `1`.
AND
SET recent = 1 WHERE ID = 555 //update specific `recent` column where its ID is `555`.

is it possible to do that in one query?

EDIT: The data type of recent is Boolean, and there should be only one that is set to be TRUE. So I want to set it all back to FALSE to make sure none of them will be true then update the row with ID of 555 to TRUE making it the only one TRUE.

Answer

You could use a case expression:

UPDATE mytable
SET    recent = CASE WHEN recent = 1 THEN 0
                     WHEN id = 555   THEN 1
                END
WHERE  recent = 1 OR id = 555

Advertisement

Answer