Replace "0" for "1" and "1" for "0" in the same SQL query

Question

I have a table named "emp" with the following data:

id        name   status
1           x         0
2           y         1
3           z         0
4           p         1

How to write a query to change status 0 into 1 and 1 into 0 in a single query?

Answer 1

With a CASE:

UPDATE emp
SET status = CASE status 
               WHEN 1 THEN 0 
               WHEN 0 THEN 1 
             END

Or, with a little math:

UPDATE emp
SET status = 1 - status