20

I have a table Table1 as follows 

col1 col2
---- ----
 A    1
 B    1
 C    1
 D    0
 E    0
 F    0

I want the result table should be as follows (by using Update statement)

col1 col2
---- ----
 A    0
 B    0
 C    0
 D    1
 E    1
 F    1
Venkateswarlu Avula
  • 341
  • 1
  • 4
  • 14
  • Depending on the second column value if it is 0 then it should update with 1 and if it is 1 then it should update with 0. I tried with update and case but it was not working. – Venkateswarlu Avula Apr 29 '12 at 03:57

6 Answers6

37

Script 1: Demo at SQL Fiddle

UPDATE dbo.Table1 SET col2 = (CASE col2 WHEN 1 THEN 0 ELSE 1 END);

Script 2: If the values are always 0 or 1, you could use the Bitwise Exclusive OR operator. Demo at SQL Fiddle

UPDATE dbo.Table1 SET col2 = (col2 ^ 1);
  • In a contrived test, against an enormous table, your Script 2 was just a hair faster than my try. Not sure we can improve on that one. Nice work! – nathan_jr Apr 29 '12 at 06:59
  • not what OP wanted, but in case anyone googles this. This also works when checking strings: UPDATE dbo.Table1 SET col1 = (CASE col1 WHEN 'A' THEN 'B' WHEN 'B' THEN 'A' ELSE col1 END); – hormberg Mar 18 '19 at 14:14
21

Since you mentioned you are using INTs, how about:

update dbo.Table1 set col2 = 1 - col2;
nathan_jr
  • 9,092
  • 3
  • 40
  • 55
15

I'm sure there is a more elegant way, but this should work:

UPDATE Table1
SET col2 = CASE col2 WHEN 0 THEN 1 WHEN 1 THEN 0 ELSE col2 END
patmortech
  • 10,139
  • 5
  • 38
  • 50
10

If you are using proper boolean (BIT) on SQL Server, you can use this: http://sqlfiddle.com/#!6/0ed3c/1

UPDATE dbo.Table1 SET col2 = ~col2;
Michael Buen
  • 38,643
  • 9
  • 94
  • 118
  • 1
    Just a nit-pick, BIT is not the same as BOOLEAN. There is no BOOLEAN type in SQL Server. – Aaron Bertrand Apr 29 '12 at 05:26
  • Yes, SQL Server has no boolean, that's why this doesn't work `SELECT * FROM Assets WHERE IsInsured`. SQL Server's BIT is not a first-class boolean as other database though (e.g. PostgreSQL). SQL Server's boolean is non-existent as it was on Fortran. BIT is introduced by SQL Server team for boolean purposes. But for all intents and purposes, SQL Server's BIT is in every bit a boolean. Entity Framework and many ORMs honor this too: http://stackoverflow.com/questions/1446054/set-model-property-to-boolean-in-entity-framework – Michael Buen Apr 29 '12 at 05:32
2

Very Simple query

update table set col= (col-1)* -1;

Explanation:

  • Case of 0:

    update table set col= (0-1)* -1; // return 1;

  • Case of 1 :

    update table set col= (1-1)* -1; // return 0;
EzLo
  • 13,780
  • 10
  • 33
  • 38
nandan kumar
  • 21
  • 2
2

you can do this :

UPDATE table SET col = 1-col
Obsidian
  • 3,719
  • 8
  • 17
  • 30
Manucyan
  • 21
  • 2