如何通过以 1 开头的顺序值和 where 条件重置列的值

Question

我有一个表格，其中包括 3 列：

ID、优先级dict_id

对于每个dict_id，它的最大优先级为 1000，从 1(顺序(开始。 当我删除某些优先级时，如何使优先级列的值按顺序排列(不使用触发器(。 例如： 原始数据

id, priority, dict_id
1, 1, 1
2, 2, 1
3, 3, 1
4, 4, 1
5, 5, 1
6, 6, 1

删除 id 3 和 5 后，表的数据：

id, priority, dict_id
1, 1, 1
2, 2, 1
4, 4, 1
6, 6, 1

我想要这样的优先级列

id, priority, dict_id
1, 1, 1
2, 2, 1
4, 3, 1
6, 4, 1

Answer 1

您可以使用UPDATE FROM语法

UPDATE yourtab a 
SET    priority = s.priority 
FROM   (SELECT id, 
row_number() 
OVER ( 
ORDER BY id) priority 
FROM   yourtab) s 
WHERE  a.id = s.id;

演示

Answer 2

我不建议这样做，因为您可以在使用ROW_NUMBER查询时轻松生成具有所需顺序的priority列，例如

SELECT
id,
ROW_NUMBER() OVER (ORDER BY id) priority,
dict_id
FROM your_table;

您可能希望避免在每次在表中添加/删除记录时支付频繁的 DML 罚款。