How to negate a bit column value in SQL

Question

How do I update a table column in order to revert its value(set true if value is false and false for true! null remains null).

Please exclude solutions where one uses case when or IIF() I want something like following

UPDATE mytable SET IsEditable = !IsEditable

Solution 1

You can use bitwise NOT operator:

update mytable set IsEditable = ~IsEditable

Solution 2

Just use this:

UPDATE mytable SET IsEditable=IsEditable^1

If you want to test it:

SELECT CONVERT(bit,0)^1, CONVERT(bit,1)^1

Solution 3

As a math solution, you can use this

update mytable set IsEditable = 1 - IsEditable;

• IsEditable = 0 => 1 - IsEditable = 1
• IsEditable = 1 => 1 - IsEditable = 0
• IsEditable = Null => 1 - IsEditable = Null

[SQL Fiddle Demo]

Comments

[Bellash]

This is good but I thing is slower since it is using bitwise xor! anyway, voted up

[Ionic]

Well I've tested it with 1.000.000.000 rows in a fast test. Both produce the same time and consumes the same cpu (STATISTICS TIME ON). Well don't think that there is a real difference. :-)

[Ionic]

Well as fast as the bitwise or. :-D

[Bellash]

sorry ! didn't test but as per definition C=A^B=(A&(~B))|(B&(~A)) which seems slower than C=~C but I think by xoring to B and B being equals to 1 there're no speed issue. Thank you @Ionic

[Ionic]

Well no problem. :-) Nice that it helped.

[Bellash]

Could not upvote twice this! But it works perfectly thank you @shA.t

[potashin]

Didn't mean to impose any style, I was just trying to reduce them to the one.

[Ionic]

I know. I just edited it to match it. :-) I normally prefer upper cased functions, for a faster/better reading. I know that you just wanted to match them. :-)

[Yair Maron]

So simple and nice! This should be the accepted answer