[Solved] How to swap rows in SQL Server

by


Put the original values in a temporary table with the IDs swapped then join to the temporary table whilst updating the table, example code below:

--drop temp table if exists
IF OBJECT_ID('tempdb..#Temp', 'U') IS NOT NULL
    DROP TABLE #Temp

--need to store the original values
SELECT
    *,CASE WHEN Room_Number=9104 then 9103 ELSE 9104 END AS New_Room_Number
    INTO #Temp
    FROM YourTable
    WHERE Room_Number in (9103,9104)

--swap values
UPDATE y
    SET Check_IN=t.Check_IN
        ,Check_OUT=t.Check_OUT
        ,FullName=t.FullName
        ,ContactNumber=t.ContactNumber
        ,Amount=t.Amount
    FROM YourTable        y
        INNER JOIN #Temp  t ON y.Room_Number =t.New_Room_Number
    WHERE y.Room_Number in (9103,9104)

15

solved How to swap rows in SQL Server