Case Statement in Update Command SQL Server

In this blog we learn how to use case statement in update command.

Problem

I have a table which has a two columns names and status respectively. I have to update status column. If status column contain true then update it with false else update it true.

Solution

Let's create a table and name it NewTable.

CREATE TABLE [dbo].[NewTable](
[Names] [varchar](50) NULL,
[status] [bit] NULL
)

Let's insert some data in table.

For true

declare @i int=0
while (@i<100)
begin
insert into NewTable values('test'+CONVERT(varchar(3), @i)+'',1)
set @i=@i+1
end

For false

declare @i int=100
while (@i<200)
begin
insert into NewTable values('test'+CONVERT(varchar(3), @i)+'',0)
set @i=@i+1
end

Now, I am going to write a query for update.

Update NewTable set status= case status when 1 then 0 when 0 then 1 end

Run this query and see the output. It works as expected.

 

I hope you enjoy this blog.

Happy coding :)