将列值与另一条记录的同一列交换

Question

我有一个相当简单的表，包含ID、Position和Name列。

ID  Position  Name
1   1         RecordX
2   3         RecordY
3   2         RecordZ

Position列用作按用户定义的顺序显示记录的索引，它应该是唯一的，不能低于1，也不能高于表中的记录数，在这种情况下为3。该列不强制执行唯一性，因此暂时可以有两个具有相同Position的记录，但最终不应该有两个记录具有相同的位置，以便程序正确工作。目前，为了交换两条记录的位置，我需要做3个查询，即：

1. 查找另一条记录的ID

2. 更新当前记录的Position以匹配其他记录的Position

3. 用先前找到的ID更新另一条记录的Position(由于暂时会有两条记录具有相同的Position，所以用Position更新是不可能的

我觉得应该有一种方法可以做到这一点，减少对数据库的轮次，从而减少3个查询。我应该如何处理这个问题？

Answer 1

单个"交换"操作。。。

SWAP(@old_pos，@new_pos(

UPDATE
  my_table
SET
  position = CASE WHEN position = @old_pos THEN @new_pos ELSE @old_pos END
WHERE
  position IN (@old_pos, @new_pos)

不过，这并不容易扩展为交换操作表。这是因为它会尝试同时进行所有的掉期，而实际上掉期必须按照特定的顺序进行。。。

此外，如果要执行SWAP(@id，@new_pos(，则需要对正在更新的表执行子查询或自联接。MySQL不喜欢这样，尽管有一些方法可以绕过限制，但它会让事情变得有点混乱。。。

UPDATE
  my_table
INNER JOIN
  (SELECT position AS old_pos, @new_pos AS new_pos FROM (SELECT position FROM my_table WHERE id = @id)) AS params
    ON my_table.position IN (params.old_pos, params.new_pos)
SET
  myTable.position = CASE WHEN position = old_pos THEN new_pos ELSE old_pos END

(我认为会起作用(

注意：

这两个都假设@old_pos和@new_pos，或者@id和@new_pros都被找到了，但它不会检查，如果它们不存在，将弄得一团糟。

这可以通过将其放入事务中来解决，如果ROW_COUNT((显示只有1条记录被更新，则可以回滚。

Answer 2

SET @new_pos_for_id_1:=3, @new_pos_for_id_3:=1;
UPDATE my_table
    JOIN (
        SELECT 1 as id, @new_pos_for_id_1 as new_position
        UNION ALL
        SELECT 3 as id, @new_pos_for_id_3 as new_position) as positions
    USING (id)
SET position = new_position

此查询可用于一次更改多行的位置。我也喜欢@Dems的解决方案。

UPD:

解释

SELECT 1 as id, 3 as new_position
UNION ALL
SELECT 3 as id, 1 as new_position

是一个由两列组成的动态构建的表：id, new_position，其中每个id都映射到某个新的预期位置。我只是在公共id字段上JOIN具有my_table的表，并用构造的表中的值替换my_table中的值。

Answer 3

这可能适用于任何DBMS。

-- create some data
DROP TABLE ztable CASCADE;
CREATE TABLE ztable
    ( id integer NOT NULL PRIMARY KEY
    , val INTEGER 
    );      
INSERT INTO ztable(id,val) VALUES (1,1), (2,3), (3,2);
SELECT * FROM ztable;
UPDATE ztable t1
SET val=t2.val
FROM ztable t2
WHERE t1.id IN (2,3)
AND t2.id IN (2,3)
AND t1.id <> t2.id
    ;       
SELECT * FROM ztable;

结果：

CREATE TABLE
INSERT 0 3
 id | val 
----+-----
  1 |   1
  2 |   3
  3 |   2
(3 rows)
UPDATE 2
 id | val 
----+-----
  1 |   1
  2 |   2
  3 |   3
(3 rows)