TECHNOLOGIES:

# SQL Server - A (0 to 1 and 1 to 0) Convert Puzzle to Solve

Article:
Viewed:  1499
Posted On:  11/03/2015 09:57:12
How to update record from 0 to 1 & 1 to 0 in Sql Server ?

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.

 Comment: