Swap Values for Two Rows in the Same Table in SQL Server

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

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)

SQL UPDATE statement to switch two values in two rows

If 'Peter' and 'Steve' are unique in your table, this will do:

UPDATE TableX
SET ord = ( SELECT MIN(ord) + MAX(ord) 
            FROM TableX 
            WHERE name IN ('Peter', 'Steve')
          ) - ord
WHERE name IN ('Peter', 'Steve')

or (improved by @Erwin):

UPDATE TableX
SET ord = ( SELECT SUM(ord) 
            FROM TableX 
            WHERE name IN ('Peter', 'Steve')
          ) - ord
WHERE name IN ('Peter', 'Steve')

Swap two rows using sql query

use a case statement, e.g.:

update ticket
set ticket_index = case when ticket_index = :x then :y else :x end
where ticket_index in (:x, :y);

How to swap rows in SQL Server

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)

how to update swap values of two rows with single query

you can see the solution in this article

http://www.microshell.com/database/sql/swap-values-in-2-rows-sql/

look at the : The elegant way , make a join to get the data from the 2 rows to be swapped in 1 row, after that make an update is easy.

example :

UPDATE
rules AS rule1
JOIN rules AS rule2 ON
( rule1.rule_id = 1 AND rule2.rule_id = 4 )
SET
rule1.priority = rule2.priority,
rule2.priority = rule1.priority
;

How to compare two row in same table and return the data in response using stored procedure

You need to unpivot all the columns, then join each row to every other.

You can either pivot everything manually using CROSS APPLY (VALUES

SELECT
  aId = a.id,
  bId = b.id,
  v.columnName,
  v.value1,
  v.value2
FROM @t a
JOIN @t b
    ON a.id < b.id
 -- alternatively
 -- ON a.id = 1 AND b.id = 2
CROSS APPLY (VALUES
   ('col1', CAST(a.col1 AS nvarchar(100)), CAST(b.col1 AS nvarchar(100))),
   ('col2', CAST(a.col2 AS nvarchar(100)), CAST(b.col2 AS nvarchar(100))),
   ('col3', CAST(a.col3 AS nvarchar(100)), CAST(b.col3 AS nvarchar(100))),
   ('col4', CAST(a.col4 AS nvarchar(100)), CAST(b.col4 AS nvarchar(100)))
) v (ColumnName, Value1, Value2)
WHERE EXISTS (SELECT v.Value1 EXCEPT SELECT v.Value2)
FOR JSON PATH;

The use of WHERE EXISTS (SELECT a.Value1 INTERSECT SELECT a.Value2) means that nulls will get taken into account properly.

---

Or you can use SELECT t.* FOR JSON and unpivot using OPENJSON

WITH allValues AS (
    SELECT
      t.id,
      j2.[key],
      j2.value,
      j2.type
    FROM @t t
    CROSS APPLY (
        SELECT t.*
        FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
    ) j1(json)
    CROSS APPLY OPENJSON(j1.json) j2
    WHERE j2.[key] <> 'id'
)
SELECT
  aId = a.id,
  bId = b.id,
  columnName = a.[key],
  value1 = a.value,
  value2 = b.value
FROM allValues a
JOIN allValues b ON a.[key] = b.[key]
    AND a.id < b.id
 -- alternatively
 -- AND a.id = 1 AND b.id = 2
WHERE a.type <> b.type
   OR a.value <> b.value
FOR JSON PATH;

db<>fiddle

SQL Fiddle of actual data

Swapping two rows of the same table based on ROWID in Oracle

You can use the merge statment to swap values between two rows in one sql command:

merge into duty_rota d
using (select * from duty_rota ) s
on ((d.rowid = 'AA1' and s.rowid = 'AA2') or (d.rowid = 'AA2' and s.rowid = 'AA1'))
when matched then update set d.duty_date = s.duty_date 

---

Related Topics