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
update tableX set bitFieldY = bitFieldY ^ 1 where ...
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.
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
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
x = "select x from table"
"update table set x=" Not(x*(1))