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

Discover how to efficiently swap column values between '1' and '0' in a SQL table using a single query. Learn the art of data manipulation within SQL to update specific column values based on conditions, enhancing your database management skills with precise and effective techniques.

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
   
   Explanation: (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