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:
  1. -- Original Record
  2. SELECT * FROM INDICATOR
  4. -- Update Command
  5. CREATE TABLE #TMPWith0
  6. (
  7. Value INT
  8. )
  9. CREATE TABLE #TMPWith1
  10. (
  11. Value INT
  12. )
  13. INSERT INTO #TMPWith0 SELECT * FROM Indicator WHERE Value=0
  14. INSERT INTO #TMPWith1 SELECT * FROM Indicator WHERE Value=1
  16. UPDATE #TMPWith0 SET Value=1
  17. UPDATE #TMPWith1 SET Value=0
  19. DELETE FROM Indicator
  21. INSERT INTO Indicator SELECT * FROM #TMPWith0
  22. INSERT INTO Indicator SELECT * FROM #TMPWith1
  24. DROP TABLE #TMPWith0
  25. DROP TABLE #TMPWith1
  27. --Record After Update
  28. SELECT * FROM INDICATOR