SQL UPDATE 语句在两行中切换两个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8108798/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL UPDATE statement to switch two values in two rows
提问by ahmd0
I'm using SQL Server to swap two values in two rows. Let me show:
我正在使用 SQL Server 交换两行中的两个值。让我展示：
[ord] [name]
1 John
4 Hyman
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:
说，我需要交换“Pete”和“Steve”的 [ord] 数字才能使这张桌子像这样：
[ord] [name]
1 John
4 Hyman
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.
这似乎是一项微不足道的任务，但我似乎无法为它编写 SQL UPDATE 语句。
回答by ypercube??
If 'Peter'and 'Steve'are unique in your table, this will do:
如果'Peter'和'Steve'在您的表中是唯一的，这将执行以下操作：
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):
或（由@Erwin 改进）：
UPDATE TableX
SET ord = ( SELECT SUM(ord)
FROM TableX
WHERE name IN ('Peter', 'Steve')
) - ord
WHERE name IN ('Peter', 'Steve')
回答by Mark Byers
回答by Erwin Brandstetter
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.comfor you.
这与您之前的问题非常相似：SQL to move rows up or down in two-table排列
我在 data.stackexchange.com 上为您准备了另一个演示。
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.
此查询仅在可以找到两行时更新，否则不执行任何操作。
回答by Branko Dimitrijevic
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...
您可以轻松地将“Pete”和“Steve”替换为其他名称...
回答by BILAL WANI
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
回答by mjwills
BEGIN TRANSACTION
UPDATE TABLENAME
SET ord = 9
where name = 'Pete'
UPDATE TABLENAME
SET ord = 7
where name = 'Steve'
COMMIT TRANSACTION
