SQL UPDATE statement to switch two values in two rows

Question

I'm using SQL Server to swap two values in two rows. Let me show:

[ord] [name]
1     John
4     Jack
7     Pete
9     Steve
11    Mary

Say, I need to swap [ord] numbers for "Pete" and "Steve" to make this table to be like so:

[ord] [name]
1     John
4     Jack
9     Pete
7     Steve
11    Mary

This seems like a trivial task but I can't seem to write an SQL UPDATE statement for it.

ypercubeᵀᴹ · Accepted Answer · 2011-11-13T02:45:19.787

24

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')

edited Nov 13 '11 at 02:45

answered Nov 13 '11 at 01:55

ypercubeᵀᴹ

113,259
19
174
235

What I wanted to write to begin with: +1 for solving this with an aggregation. I wanted to do that, too, but gave up, because I could not find an equivalent of [array_agg()](http://www.postgresql.org/docs/9.1/interactive/functions-aggregate.html) in tSQL. – Erwin Brandstetter Nov 13 '11 at 02:29
1

min + max works of course, sorry! I could have sworn I had seen max + max. I copy / pasted it into my comment and for everyone to see, it's min + max. Sorry for the mixup. I deleted my wrong comments. Anyway. sum() does the same and is faster. – Erwin Brandstetter Nov 13 '11 at 02:34
Very much appreciated, guys! It's such a neat solution. For some reason I thought that MIN/MAX or SUM computes the value on the entire table and not the selection. In any way, works perfect! Thanks. – ahmd0 Nov 13 '11 at 03:27
Just note that the solutions from Mark or mjwills may be better in terms of "safe code", if you include code to check that there are actually 2 records that match the `WHERE`. To be honest, I wouldn't run this code of mine in a production system. If you happen to have only "Steve" and not "Peter" in your table, Steve's ord will be zeroed. – ypercubeᵀᴹ Nov 13 '11 at 03:47
1

The only *safe* piece of code here so far is my solution. If one or both values in `ord` is NULL, they trade places like any other value. If one of the rows does not exist, nothing is changed. That's how it should be, IMO. – Erwin Brandstetter Nov 13 '11 at 07:08

score 9 · Answer 2 · answered Nov 13 '11 at 01:10

9

Use a CASE expression:

UPDATE yourtable
SET [ord] = CASE [ord] WHEN 9 THEN 7
                       WHEN 7 THEN 9 END
WHERE [ord] IN (7, 9)

answered Nov 13 '11 at 01:10

Mark Byers

811,555
193
1,581
1,452

1

This is only a simplified subset of the problem. It assumes that you query `ordinal number` of Pete and Steve first and build the query with the results. – Erwin Brandstetter Nov 13 '11 at 01:28

score 7 · Answer 3 · edited Jan 18 '21 at 12:30

7

This is very similar to your earlier question: SQL to move rows up or down in two-table arrangement
I prepared another demo on data.stackexchange.com for you.

Edit: the setup is simplified now, so I simplified my query accordingly.

WITH x AS (SELECT name, ord FROM t WHERE name = 'Pete')  -- must be unique!
   , y AS (SELECT name, ord FROM t WHERE name = 'Steve') -- must be unique!
UPDATE t
SET    ord = z.ord
FROM  (
   SELECT x.name, y.ord FROM x,y
   UNION  ALL
   SELECT y.name, x.ord FROM x,y
   ) z
WHERE t.name = z.name;

This query only updates if both rows can be found and does nothing otherwise.

edited Jan 18 '21 at 12:30

Community

1
1

answered Nov 13 '11 at 01:24

Erwin Brandstetter

605,456
145
1,078
1,228

1

Haha. You know, I posted this question to actually better understand your other post. )) Now I know what you meant with that code. Thanks again. Unfortunately this time I'd give the "answer" to ypercube above. It's a very neat solution with MAX/MAX or SUM. – ahmd0 Nov 13 '11 at 03:26
@ahmd0: Well, I agree. Upvoted it myself. :) min/max, btw., not max/max. Funny, I had the same mixup ... – Erwin Brandstetter Nov 13 '11 at 03:38

score 4 · Answer 4 · answered Nov 13 '11 at 03:20

UPDATE Table_1
SET ord =
    CASE name
    WHEN 'Pete' THEN (SELECT ord FROM Table_1 WHERE name = 'Steve')
    WHEN 'Steve' THEN (SELECT ord FROM Table_1 WHERE name = 'Pete')
    END
WHERE name IN ('Pete', 'Steve')

You can easily replace 'Pete' and 'Steve' with other names...

score 0 · Answer 5 · answered Feb 22 '20 at 11:07

Use below script to swap values


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

score 0 · Answer 6 · answered Nov 13 '11 at 01:28

0

BEGIN TRANSACTION

UPDATE TABLENAME
SET ord = 9 
where name = 'Pete'

UPDATE TABLENAME
SET ord = 7
where name = 'Steve'

COMMIT TRANSACTION

answered Nov 13 '11 at 01:28

mjwills

23,389
6
40
63

SQL UPDATE statement to switch two values in two rows

6 Answers6

Linked