How to flip bit fields in T-SQL?

I'm trying to flip a bit field in SQL Server using an update query, that is, I want to make all the 0's into 1's and vice versa. What's the most elegant solution?

There doesn't seem to be a bitwise NOT operator in T-SQL (unless I'm missing something obvious) and I haven't been able to find any other way of performing the update.

You don't need a bitwise-not for this -- just XOR it with 1 / true.

To check it:

select idColumn, bitFieldY, bitFieldY ^ 1 as Toggled
from tableX

To update:

update tableX
set bitFieldY = bitFieldY ^ 1
where ...

http://msdn.microsoft.com/en-us/library/ms190277(SQL.90).aspx">MSDN T-SQL Exclusive-OR (^)

Why not a simple bitfield = 1 - bitfield?

Another way is

DECLARE @thebit bit = 1, @theflipbit bit

SET @theflipbit = ~ @thebit

SELECT @theflipbit

where "~" means "NOT" operator. It's clean and you get a good code to read. "negate the bit" is even cleaner and it does exactly what the "NOT" operator was designed for.

I was pretty sure that most SQL flavors had a bitwise NOT, so I checked and there does appear to be one in TSQL.

From the documentation, it's the character ~.

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

It's bland but everyone will understand what it's doing.

EDIT:

You might also need to account for nulls as suggested in the comments. Depends on your req's of course.

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

A simple bitwise NOT operator (~) worked for me in SQL Server 2014 - 12.0.2269.0

In the update clause inside your T-SQL -

        Update TableName
        SET    [bitColumnName] = ~[bitColumnName],
			   ....
        WHERE  ....

Hope this helps

Ref - https://docs.microsoft.com/en-us/sql/t-sql/language-elements/bitwise-not-transact-sql

Did you try this?

UPDATE mytable SET somecolumn = 
  CASE WHEN somecolumn = 0 THEN 1 
       WHEN somecolumn IS NULL THEN NULL
       WHEN somecolumn = 1 THEN 0
  END

query (vb)

x = "select x from table"

update (vb)

"update table set x=" Not(x*(1))