Tips for toggle, or flip, bit columns

Some tips I record here just to get them in touch.

Looking for a toggle function in T-SQL, I've found these tips in StackOverflow:

update table_name set status = status ^ 1  where code = 123 [Using XOR]
update table_name set status = 1 - status  where code = 123 [Using minus logically]
In my case, I had a nullable field, with many nulls inside, so I had to adapt:
update table_name set status = isnull(status,0) ^ 1  where code = 123
update table_name set status = 1 - isnull(status,0)  where code = 123

I have never needed to do this directly to the database, but only in high level tiers.

Usually I would do it in a more readable way, like in Mayo contribution:

UPDATE tblTest SET MyBitField = CASE WHEN MyBitField = 1 THEN 0 ELSE 1 END

and...

UPDATE tblTest SET 
   MyBitField = CASE 
      WHEN MyBitField = 1 THEN 0 
      WHEN MyBitField = 0 THEN 1
      ELSE NULL -- or 1 or 0 depending on requirements
   END

Thanks to gbn, Mayo and Austin Salonen. You did great.

Source: http://stackoverflow.com/questions/1397332/how-to-flip-bit-fields-in-t-sql