Email: Password:       Forgot Password 
    .netCodeSG
A Saarsha Group Online Community for dot net codes group like C#, Asp.NET, VB.NET, Sharepoint, JavaScript, JQuery, Ajax, SQL, WCF, WPF.
 
TECHNOLOGIES:
 

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

Article:
Viewed:  741 
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.

1.png

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 

2.png

Image 2.

Solution 2# Use Mathematical Logic

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

 

3.png

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

 

4.png

Image 4.

  Comment:
         HOME   |   Submit Article   |   Contact Us   |   About Us   |   Terms & Condition   |   Advertise With us