在SQL Server中的同一个表中交换两行的值

I have table and I want to swap the values from two rows. 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

I don't want write it in .NET code because I think an SQL query is easier than that.

我不想在.NET代码中编写它，因为我认为SQL查询比这更容易。

7 个解决方案

#1

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)

#2

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.

结果：交换行值。

#3

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列，我相信您可以通过任何列进行搜索，并使用正确的连接逻辑。将表连接到自身就是诀窍。

#4

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

表：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)

#5

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

#6

I created one table with columns name and gender. I inserted values in it. Now I want to interchange values in gender column as M=F,F=M.

我创建了一个包含列名和性别的表。我在其中插入了值。现在我想将性别列中的值互换为M = F，F = M.

#7

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

#1