This is SQL Server puzzle or an interview question asked many times. I want to dedicate this post to one of my lead Saab. Whenever he took any interview he asked this question for sure shot.
Now the puzzle is you have a table with some records 0 & 1. You have to replace column value 1 with 0 and 0 with 1 like below.
Image 1.
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
Image 2.
Solution 2# Use Mathematical Logic
UPDATE INDICATOR SET VALUE= (VALUE -1) * -1
Image 3.
Solution 3 # Use Temp Table
-- 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
Image 4.