Swap values for two rows in the same table in SQL Server

Question

I want to swap the values from two rows in a table. I have the rows IDs of the two rows. Is there any query to do that? Here is an example. Before the query I have this:

row1 : 1,2,3
row2 : 5,6,7

After the swap I want this:

row1 : 5,6,7
row2 : 1,2,3

Answer 1

If you want to swap values from one row to the other for two known IDs try something like this:

--need to store the original values
SELECT
    *,CASE WHEN id=123 then 987 ELSE 123 END AS JoinId
    INTO #Temp
    FROM YourTable
    WHERE ID in (123,987)

--swap values
UPDATE y
    SET col1=t.col1
        ,col2=t.col2
    FROM YourTable        y
        INNER JOIN #Temp  t ON y.id =t.JoinId
    WHERE ID in (123,987)

Answer 2

Simple update works:

UPDATE myTable
SET 
col1 = CASE WHEN col1 = 1 THEN 5 ELSE 1 END,
col2 = CASE WHEN col2 = 2 THEN 6 ELSE 2 END,
col3 = CASE WHEN col3 = 3 THEN 7 ELSE 3 END 

Result: row values are swapped.