SQL Server: A (0-1 & 1-0) Convert Puzzle to Solve

Introduction

This is a SQL Server puzzle or an interview question asked often. I want to dedicate this article to one of my leads, Saab. Whenever he did an interview he asked this question for sure shot.

Now the puzzle is you have a table with some records, 0 & 1. You need to replace column value 1 with 0 and 0 with 1 as in the following.



Now we have many ways to solve this.

Solution 1

Use CASE

UPDATE INDICATOR SET VALUE= CASE VALUE WHEN 1 THEN 0 WHEN 0 THEN 1 END

Solution 2

UPDATE INDICATOR SET VALUE= (VALUE -1) * -1



Solution 3

 

Use a Temp table as in the following:

-- Original Record
SELECT * FROM INDICATOR

-- Update Command
CREATE TABLE #TMPWith0
(
   Value INT
)
CREATE TABLE #TMPWith1
(
    Value INT
)
 INSERT INTO #TMPWith0 SELECT * FROM Indicator WHERE Value=0
 INSERT INTO #TMPWith1 SELECT * FROM Indicator WHERE Value=1

 UPDATE #TMPWith0 SET Value=1
 UPDATE #TMPWith1 SET Value=0

 DELETE FROM Indicator

 INSERT INTO Indicator SELECT * FROM #TMPWith0
 INSERT INTO Indicator SELECT * FROM #TMPWith1

 DROP TABLE #TMPWith0
 DROP TABLE #TMPWith1

--Record After Update
SELECT * FROM INDICATOR