交换 SQL Server 中同一表中两行的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2758415/
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
Swap values for two rows in the same table in SQL Server
提问by Amr Elnashar
I want to swap the values from two rows in a table. I have the rows IDs of the two rows. Is there any query to do that? Here is an example. Before the query I have this:
我想交换表中两行的值。我有两行的行 ID。是否有任何查询可以做到这一点？这是一个例子。在查询之前我有这个：
row1 : 1,2,3 row2 : 5,6,7
After the swap I want this:
交换后我想要这个：
row1 : 5,6,7 row2 : 1,2,3
采纳答案by KM.
If you want to swap values from one row to the other for two known IDs try something like this:
如果要将两个已知 ID 的值从一行交换到另一行，请尝试以下操作：
--need to store the original values
SELECT
*,CASE WHEN id=123 then 987 ELSE 123 END AS JoinId
INTO #Temp
FROM YourTable
WHERE ID in (123,987)
--swap values
UPDATE y
SET col1=t.col1
,col2=t.col2
FROM YourTable y
INNER JOIN #Temp t ON y.id =t.JoinId
WHERE ID in (123,987)
回答by Israel Margulies
Simple update works:
简单的更新工作：
UPDATE myTable
SET
col1 = CASE WHEN col1 = 1 THEN 5 ELSE 1 END,
col2 = CASE WHEN col2 = 2 THEN 6 ELSE 2 END,
col3 = CASE WHEN col3 = 3 THEN 7 ELSE 3 END
Result: row values are swapped.
结果：行值被交换。
回答by Brian
UPDATE t1
SET
t1.col1 = t2.col1
,t1.col2 = t2.col2
,t1.col3 = t2.col3
,t1.col4 = t2.col4
--and so forth...
FROM YourTable AS t1
INNER JOIN YourTable AS t2
ON (t1.ID = '1'
AND t2.ID = '2')
OR
(t1.ID = '2'
AND t2.ID = '1')
You don't necessarily need to use the ID column of your table, I believe you could search by any column, with the proper joining logic. Joining the table to itself is the trick.
您不一定需要使用表的 ID 列，我相信您可以使用适当的连接逻辑按任何列进行搜索。将桌子连接到自己是诀窍。
回答by MarredCheese
If you only need to swap a couple of rows, then yeah, you can brute force it with tailor-made case statements and join statements like in the other answers. But if you need to operate on many rows, that's going to be a pain.
如果您只需要交换几行，那么是的，您可以使用量身定制的 case 语句和连接语句来强制执行它，就像在其他答案中一样。但是如果你需要对多行进行操作，那将会很痛苦。
A Simple, Scalable Solution
一个简单、可扩展的解决方案
WITH map AS (
SELECT *
FROM (VALUES
(1, 2), -- Here's an example of swapping two rows:
(2, 1), -- 1 <- 2, 2 <- 1
(3, 4), -- Here's an example of rotating three rows:
(4, 5), -- 3 <- 4, 4 <- 5, 5 <- 3
(5, 3),
(6, 7) -- Here's an example of just copying one row to another: 3 <- 5
) AS a (destID, srcID)
)
UPDATE destination
SET
ColumnA = source.ColumnA,
ColumnB = source.ColumnB,
ColumnC = source.ColumnC
FROM
SomeTable AS destination
JOIN map ON map.destID = destination.ID
JOIN SomeTable AS source ON source.ID = map.srcID
Notes
笔记
- You can do two-row swaps, many-row swaps, and copies. It's flexible.
- Specify as many destination/source row pairs as needed. Only destination rows will be updated.
- Specify the columns you want to be copied over. Only those columns will be updated.
- There's no temporary table to clean up.
- It's easy to reuse since the row IDs are listed in a single, obvious place.
- 您可以进行两行交换、多行交换和复制。它很灵活。
- 根据需要指定尽可能多的目标/源行对。只会更新目标行。
- 指定要复制的列。只会更新那些列。
- 没有要清理的临时表。
- 因为行 ID 列在一个明显的地方，所以很容易重用。
回答by Nikunj Patel
You need to select all records by "WHERE" condition, Then "SET" update by "CASE" condition.
您需要通过“WHERE”条件选择所有记录，然后通过“CASE”条件“SET”更新。
UPDATE tbl_Temp SET
fk_userType = CASE fk_userType WHEN 1 THEN 2 WHEN 2 THEN 1 END,
fk_userRole = CASE fk_userRole WHEN 1 THEN 2 WHEN 2 THEN 1 END
WHERE (fk_userType = 1 AND fk_userRole = 1) OR (fk_userType = 2 AND fk_userRole = 2);
回答by U?ur PARLAYAN
None of the above examples is practical ... It should look like the following update section:
以上示例均不实用......它应该类似于以下更新部分：
/*******************************************************************************/
/* DATA TABLE IS PREPARING */
/*******************************************************************************/
IF EXISTS (SELECT TOP 1 * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TEST' AND TABLE_SCHEMA = 'dbo')
DROP TABLE [dbo].[TEST];
CREATE TABLE [dbo].[TEST](
[ID] int IDENTITY(1,1) NOT NULL,
[Name] varchar(50) NULL,
[Surname] varchar(50) NULL,
[AGE] int NULL,
CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED
( [ID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/*******************************************************************************/
/* INSERTING TEST VALUES */
/*******************************************************************************/
INSERT INTO dbo.TEST (Name, Surname, AGE)
SELECT 'Sevim' , 'PARLAYAN' , 36 UNION ALL
SELECT 'U?ur' , 'PARLAYAN' , 41 UNION ALL
SELECT 'Berkan Cahit' , 'PARLAYAN' , 17 UNION ALL
SELECT 'Miray ?a?la' , 'PARLAYAN' , 6 ;
SELECT * FROM dbo.TEST ORDER BY ID;
-- At this point maybe the trigger can be disabled...
/*******************************************************************************/
/* I'm swapping U?ur and Sevim rows (So, rows into 1 and 2 do swapping )... */
/*******************************************************************************/
UPDATE TT
SET TT.Name = ZZZ.Name
, TT.Surname = ZZZ.Surname
, TT.AGE = ZZZ.AGE
FROM dbo.TEST as TT
JOIN (
SELECT TOP 1 * FROM dbo.TEST WHERE ID = 2 /* Big key value first */ UNION ALL
SELECT TOP 1 * FROM dbo.TEST WHERE ID = 1 /* Then small key value... */
) as ZZZ on ZZZ.ID in (1, 2)
WHERE TT.ID in (1, 2) ;
-- At this point maybe the trigger can be activated...
SELECT * FROM dbo.TEST ORDER BY ID
回答by penguinjeff
I had a similar issue recently I had a column for ordering the output and wanted to allow moving the order around. I was looking for the answer and ran across this question. This didn't sufficiently answer my particular query but maybe my solution will help others.
我最近遇到了类似的问题，我有一个用于订购输出的列，并希望允许移动订单。我正在寻找答案并遇到了这个问题。这并没有充分回答我的特定查询，但也许我的解决方案会帮助其他人。
I had my database look like so
我的数据库看起来像这样
Table:Order_Table
表：订单_表
Index_Column,Order_Column,Text
1 ,1 ,"Second Test text"
2 ,2 ,"First Test text"
I wanted to be able to swap them around using pdo in php. Ultimately I found a way to do it with one SQL query
我希望能够在 php 中使用 pdo 来交换它们。最终我找到了一种使用 SQL 查询的方法
UPDATE `Order_Table` AS o
INNER JOIN (SELECT `Index_Column`, `Order_Column` FROM `Order_Table`
WHERE `Index_Column` IN (:Index1,:Index2))
AS t ON o.`Index_Column` <> t.`Index_Column`
SET o.`Order_Column` = t.`Order_Column`
WHERE o.`Index_Column` IN (:Index1,:Index2)
