CODEWITHSUNDEEP
sql-server
Milacay
Milacay

Reputation: 1497

Reverse Data With BIT TYPE for SQL Server

I have a column using a BIT type (1/0). I have some records are set to 1 and some are set to 0. Those are record flag needs to be reversed. So basically, I want all records with 1 set 0, and all records with 0 set to 1.

If I run 

Update Table1 Set Flag = 1 Where Flag = 0

first, then I am afraid all record flags will be 1 now, and will not able to know which ones are flag = 0.

Any suggestions?

Thanks!

Upvotes: 3

Views: 2442

Answers (5)

HABO
HABO

Reputation: 15816

There's the simple arithmetic:

update table1
  set Flag = 1 - Flag

Martin Smith's second answer can be reduced to a somewhat more obscure:

update table1
  set Flag ^= 1

Great for the keystroke challenged, but I don't think it improves on readability, maintainability or performance.

Upvotes: 2

Andy Mudrak
Andy Mudrak

Reputation: 807

Here's a way you can do it without a CASE statement, using a bitwise operator instead:

update Table1
set flag = ~flag

Hope this helps!

Upvotes: 6

Charles Bretana
Charles Bretana

Reputation: 146499

 Update table1 
  Set flag = Case flag 
      When 1 then 0 
      Else 1 End

Upvotes: 0

Hiren Dhaduk
Hiren Dhaduk

Reputation: 2780

Try following Query :

update table1
set flag = case when flag = 1 then 0 else 1 end

It will definitely solve your problem .

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453358

To negate the value of all bit fields do it in one operation. You can use a CASE

UPDATE table1
SET    flag = CASE flag
                WHEN 1 THEN 0
                WHEN 0 THEN 1
              END

Or Bitwise Exclusive OR

UPDATE table1
SET    flag = flag ^ 1

Upvotes: 4

Related Questions