update query for entire column [Resolved]

Posted by Sriharim under Sql Server on 6/5/2015 | Points: 10 | Views : 733 | Status : [Member] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Mark as Answer if its helpful to you
---
Srihari

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

Latest Posts