update query for entire column [Resolved]

Posted by Sriharim under Sql Server on 6/5/2015 | Points: 10 | Views : 367 | Status : [Member] | Replies : 1
i want update query, for below table:
0 value change to 1 and 1 value change to 0, for status column

ID Name status
1 a 1
2 b 1
3 c 0
4 d 1
5 e 0


status column is to be updated as below


ID Name status
1 a 0
2 b 0
3 c 1
4 d 0
5 e 1


---
Srihari



Responses

Posted by: A2H on: 6/5/2015 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
I guess you status column datatype is bit. if that is the case then you can use the XOR operator to toggle your bit flag like given below
--Update the values  
UPDATE [SampleTable] SET [Status] = [Status] ^ 1

Change tablename as per your design.

Complete Code:
CREATE TABLE [dbo].[SampleTable](
[ID] [int] NULL,
[Name] [nchar](10) NULL,
[Status] [bit] NULL
);

--Insert some value in table
INSERT INTO [SampleTable]([ID],[Name],[Status])VALUES(1,'A','TRUE')
INSERT INTO [SampleTable]([ID],[Name],[Status])VALUES(2,'B','TRUE')
INSERT INTO [SampleTable]([ID],[Name],[Status])VALUES(3,'C','FALSE')
INSERT INTO [SampleTable]([ID],[Name],[Status])VALUES(4,'D','TRUE')
INSERT INTO [SampleTable]([ID],[Name],[Status])VALUES(5,'E','TRUE')

--Update the values
UPDATE [SampleTable] SET [Status] = [Status] ^ 1

Select * from SampleTable


You can see a working demo here : http://sqlfiddle.com/#!3/db175/1



Thanks,
A2H
My Blog

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response