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.

Solution 1

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

Solution 2

Use a CASE expression:

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

Solution 3

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.

Solution 4

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...

View more solutions

Comments

[Erwin Brandstetter]

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]

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() in tSQL.

[Erwin Brandstetter]

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.

[ahmd0]

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]

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.

[Erwin Brandstetter]

@ahmd0: Well, I agree. Upvoted it myself. :) min/max, btw., not max/max. Funny, I had the same mixup ...

[ypercubeᵀᴹ]

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.

[Erwin Brandstetter]

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.