CODEWITHSUNDEEP
sqlsql-server
Raveendra Bj
Raveendra Bj

Reputation: 49

Write a query to swap values in a table in SQL Server?

I have mistakenly inserted wrong data in table rows, now I want to swap the data. Male in place of Female and vice-versa.

enter image description here

Following is the correct data I am expecting -

enter image description here

Upvotes: 4

Views: 7869

Answers (4)

BILAL WANI
BILAL WANI

Reputation: 1

Use below script to swap values between 2 rows

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL DROP TABLE #TempTable

CREATE TABLE #TempTable
(
     ROW_ID INT IDENTITY(1,1),
     SEQUENCE_NO    INT,
     ID INT
)

DECLARE @Id INT = 24780, --Row Id

DECLARE @NewPosition INT = -1; -- (Move Up or Move Down +1 for Up and -1 For Down)
DECLARE @SEQUENCE_NO INT = 0;  

INSERT INTO #TempTable
SELECT  SEQUENCE_NO ,ID
            FROM TABLE_NAME S
    WHERE ID = @Id 

SET @SEQUENCE_NO = (SELECT SEQUENCE_NO FROM #TempTable)

INSERT INTO #TempTable
SELECT  SEQUENCE_NO AS SNO,ID
            FROM TABLE_NAME S
    WHERE ID  <> @Id   
    AND SEQUENCE_NO = (@SEQUENCE_NO + @NewPosition) -- (Move Up or Move Down +1 for Up and -1 For Down)

--Add check point here temp table to have 2 exact records 

;WITH x AS (SELECT  ID, SEQUENCE_NO FROM #TempTable WHERE ROW_ID = 1)   
   , y AS (SELECT ID, SEQUENCE_NO FROM #TempTable  WHERE ROW_ID = 2) 
UPDATE #TempTable
SET    SEQUENCE_NO = z.SEQUENCE_NO
FROM  (
   SELECT x.ID, y.SEQUENCE_NO FROM x,y
   UNION  ALL
   SELECT y.ID, x.SEQUENCE_NO FROM x,y
   ) z
WHERE #TempTable.ID = z.ID;


UPDATE SI
    SET SI.SEQUENCE_NO = T.SEQUENCE_NO -- (Swap Values here)
    FROM TABLE_NAME SI
        JOIN #TempTable T ON SI.ID = T.ID

Upvotes: 0

Daniel Brughera
Daniel Brughera

Reputation: 1651

I don't understand why you want a temp table if you can do it with a single update

But maybe this fits what you are looking for

SELECT TOP 0 * 
INTO #tmp 
FROM YourTable

UPDATE T
SET gender = 'male'
OUTPUT Inserted.* INTO #tmp
FROM YourTable T
WHERE gender = 'female'

UPDATE T
SET gender = 'female'
FROM YourTable
WHERE gender = 'male' --Irrelevant for this case but assuming that you have different values
AND NOT EXISTS (SELECT 1 FROM #Temp WHERE name = T.name) -- Exclude the "swapped" records

Or maybe you just want a temp table to confirm the changes before update them.... i am trying to guess

DECLARE @swap1 VARCHAR(20) = 'male', @swap2 VARCHAR(20) = 'female'

SELECT * 
INTO #tmp
FROM YourTable

UPDATE #tmp 
    SET gender = CASE 
        WHEN gender = @swap1 THEN @swap2 
        WHEN gender = @swap2 THEN @swap1 
        ELSE gender END

SELECT * FROM #tmp

UPDATE A
SET Gender = T.gender
FROM YourTable A
JOIN #tmp T ON A.name = T.name

DROP TABLE #tmp

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

You could use:

UPDATE table_name
SET Gender = CASE Gender
                  WHEN 'Male' THEN 'Female'
                  WHEN 'Female' THEN 'Male'
                  ELSE Gender
             END;

LiveDemo

Please note that other values than male/female like N/A or NULL will remain the same.

If you want to do it only for specified names use:

UPDATE table_name
SET Gender = CASE
               WHEN Name IN ('Geetha', 'Radha') THEN 'Female'
               WHEN Name IN ('Ram', 'Syam', 'Ravi') THEN  'Male'
             END
WHERE Name IN ('Ram', 'Syam', 'Geetha', 'Radha', 'Ravi');

Upvotes: 2

Piyush Gupta
Piyush Gupta

Reputation: 2179

Simple update works:

UPDATE myTable
SET 
col1 = CASE WHEN col1 = 'male' THEN 'female' ELSE 'male' END,
col2 = CASE WHEN col2 = 'female' THEN 'male' ELSE 'female' END

Result: row values will be swap.

I hope, It will work for you.

Upvotes: 9

Related Questions