Swapping a column values like ‘1 to 0’ and ‘0 to 1’ of a Table using SQL line query

Here we will discuss the method of swapping column values of a Table in SQL.

Step 1: Create a table named ‘Tbl’ with a column named ‘ID’ of int type.

create table Tbl
(
ID int
)

Step 2: Insert some rows of ‘0’ value and some of ‘1’.

insert into Tbl values (0)
insert into Tbl values (1)
insert into Tbl values (0)
insert into Tbl values (1)
insert into Tbl values (0)
insert into Tbl values (1)

Step 3: Select the Table to see the table.

select * from Tbl

Question: Now I want to swap the values of the ID column, like, ’0’ will become ‘1’ and ‘1’ will become ‘0’.

Answer: You can resolve this task using several methods, but if you want to do this in the easiest way then you have 2 methods.

1. Arithmetical way -

   update Tbl set ID=(ID-1)*-1

   Explaination: (ID-1)*-1 means

   If ID=0 then value will be (0-1)*1 => -1*-1=> 1//New Value

   If ID=1 then value will be (1-1)*1 => 0*-1=> 0// New Value

   Now set the new value to ID column by the ID=// New Value

   
   

2. Conditional way-

update Tbl set ID=(case when ID=0 then 1 else 0 end)

Explaination: (case when ID=0 then 1 else 0 end) means

If ID=0 then value will replaced by 1//New Value

If ID=1 then value will replaced by 0// New Value

Now set the new value to ID column by the ID=// New Value