1

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.

2 Answers2

0

In the interest of future maintainability: Stop looking for this solution.

What's the implied/expected order?

  • Would you first set recent to 0 and then a single item (for 555) to 1?
  • Would you set the 555 record to 1, and then (with all the others) back to 0?

I guess I know the answer, but it's easy to miss this in future updates of the code. Optimize for future maintenance, not for saving a statement execution.

Build two statements, execute them within a transaction, and you'll have your readable, maintainable and understandable code.

(even Mureinik's CASE statement does not look that well readable to me, even though I kind of like the solution)

Olaf Kock
  • 46,930
  • 8
  • 59
  • 90
0

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
Mureinik
  • 297,002
  • 52
  • 306
  • 350