如何在使用 SQL 更新语句时更改更新顺序?

Question

我有一个表格，其中每一行都用唯一的索引(1,2,3,...(编号。 我想将每个条目的索引增加 4，以便为开头的四个新条目(索引 1、2、3 和 4(腾出空间。

我尝试使用：

UPDATE table SET key_index = key_index + 4;

但它会导致 ERROR，因为索引必须是，如前所述，是唯一的(将 1 增加 4 会导致 5。索引 5 可能已经存在(。

如果我可以使用 UPDATE 语句，从底部开始(从最高到最低索引(，那么就会有这样的错误。是否可以使用这样的 UPDATE 语句？

Answer 1

这可能很棘手。 我使用的一个技巧是两次更新 - 首先是一系列"安全"的值。 其次是所需值：

update t
set key_index = - key_index ;
update t
set key_index = (- key_index) + 4;

您还可以删除唯一索引/约束 - 或在某些数据库中禁用它。

Answer 2

如果您正在运行 MySQL，则可以使用简单的update，其中包含按降序值对行进行排序的order by子句。这使您只需在一个查询中执行更改，而没有任何冲突风险(因为您的值是唯一的(。

MySQL 确实支持更新语句中的order by，该行为在文档中进行了描述

如果UPDATE语句包含ORDER BY子句，则行将按子句指定的顺序更新。这在某些可能导致错误的情况下非常有用。

文档继续提供一个看起来像您的用例的示例。

所以：

UPDATE table SET key_index = key_index + 4 ORDER BY id DESC;

DB小提琴上的演示：

create table mytable (key_index int);
insert into mytable values (1), (4), (5), (8), (9);
update mytable 
set key_index = key_index + 4
order by key_index desc;
select * from mytable;
| key_index |
| --------- |
| 5         |
| 8         |
| 9         |
| 12        |
| 13        |

Answer 3

戈登回答得切中要害。但是，如果要更新key_index，请记住还要更新外部引用表中的值(如果有(。

或者，您可以尝试另一种方法。添加另一列，例如dup_key_index，值为key_index + 4。然后删除key_index列并将此新列重命名dup_key_index为key_index列。

SQL 小提琴演示

# Add new column to hold the key_index value
alter table TABLE_NAME add dup_key_index int not null;
# Update this columns value from the key_index value and increment
update TABLE_NAME set dup_key_index = key_index + 4;
# Drop the key_index
alter table TABLE_NAME drop key_index;
# Rename the new column with same name as key_index
alter table TABLE_NAME change dup_key_index key_index int primary key;

SQL Server：如果您使用的是SQL Server，则引擎会在内部处理此问题，其中以下查询工作：

update TABLE_NAME set key_index = key_index + 4;

参考这个。