Reverse Data With BIT TYPE for SQL Server

Question

I have a column using a BIT type (1/0). I have some records are set to 1 and some are set to 0. Those are record flag needs to be reversed. So basically, I want all records with 1 set 0, and all records with 0 set to 1.

If I run

Update Table1 Set Flag = 1 Where Flag = 0

first, then I am afraid all record flags will be 1 now, and will not able to know which ones are flag = 0.

Any suggestions?

Thanks!

Answer 1

Here's a way you can do it without a CASE statement, using a bitwise operator instead:

update Table1
set flag = ~flag

Hope this helps!

Answer 2

To negate the value of all bit fields do it in one operation. You can use a CASE

UPDATE table1
SET    flag = CASE flag
                WHEN 1 THEN 0
                WHEN 0 THEN 1
              END 

Or Bitwise Exclusive OR

UPDATE table1
SET    flag = flag ^ 1