SQL UPDATE statement to switch two values in two r

问题:

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.

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

回答2:

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.

回答3:

Use a CASE expression:

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

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

回答5:

BEGIN TRANSACTION

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

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

COMMIT TRANSACTION