SQL UPDATE 语句在两行中切换两个值 [英] SQL UPDATE statement to switch two values in two rows

问题描述

我正在使用 SQL Server 交换两行中的两个值.让我展示一下:

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

比如说，我需要交换Pete"和Steve"的 [ord] 数字才能使这张桌子像这样:

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

这似乎是一项微不足道的任务，但我似乎无法为其编写 SQL UPDATE 语句.

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

推荐答案

如果 'Peter' 和 'Steve' 在您的表中是唯一的，则这样做:

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

或(由@Erwin 改进):

or (improved by @Erwin):

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

这篇关于SQL UPDATE 语句在两行中切换两个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！