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

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

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
    

    ID column

  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

    New value