views:

415

answers:

6

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 TSQL (unless I'm missing something obvious) and I haven't been able to find any other way of performing the update.

+2  A: 

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

Thomas Owens
A: 

here's a few ways described http://johnnycoder.com/blog/2006/10/04/t-sql-tip-flip-bit/

John Boker
+7  A: 

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 ...

MSDN T-SQL Exclusive-OR (^)

Austin Salonen
Great! Thanks for that - it works like a charm. I obviously need to learn a bit more about how to use binary operators
Billious
Another article: http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/how-to-flip-a-bit-in-sql-server-by-using
Even Mien
+2  A: 
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
Mayo
This will set MyBitField to 1 when it starts as NULL. Not exactly flipping the bit.
Shannon Severance
I figured it was self evident... but I'll add info about handling nulls just in case.
Mayo
I would tend to agree with some of the other guys and go for the ~ or the "^ 1 ". Especially if this is a one time thing (just seems cleaner). However, if this a something that will stick around and maintainability is in question, this solution is certainly the most straight forward.
L. Moser
In my case this was a one-off query, so maintainability wasn't an issue, but yes, this would have worked equally well. I actually had written a similar query but had trouble getting the CASE statement to work properly. I now realise it was a simple syntax error. D'Oh!
Billious
A: 

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
eKek0
+2  A: 

Why not a simple bitfield = 1 - bitfield?

gbn
An ingenious and elegant approach, especially for a one-off query, where readability isn't important.
Billious