sql - 根据组内索引更新列

Question

在我们的数据库中，我们有一个名为 conditions 的表，它引用了一个名为 attributes 的表。

所以它看起来像这样(忽略其他一些与问题无关的列)

<表类="s-表"><头>idattribute_idexecution_index<正文>110001210002310001420001520002620002

理论上 attribute_id 和 execution_index 的组合应该始终是唯一的，但实际上它们不是，软件最终基本上使用 id 来决定哪个首先出现在具有相同执行索引的两个条件之间。我们想为表添加唯一性约束，但在此之前我们需要更新执行索引。所以基本上我们想按 attribute_id 对它们进行分组，按 execution_index 然后是 id 对它们进行排序，并给它们新的执行索引，这样它就变成了

<表类="s-表"><头>idattribute_idexecution_index<正文>110001210003310002420001520002620003

如果不按 attribute_id、execution_index、id 进行排序，然后每次将 execution_index 递增 1 并在 attribute_id 更改时将其重置为 1 进行迭代，我不确定如何执行此操作。 (这会起作用，但会很慢，而且有人将不得不在几十个数据库上运行这个脚本，所以我希望每个数据库花费的时间不要超过几秒钟。)

我真的很想做一些类似的事情

UPDATE c
SET c.execution_index = [this needs to be the index within the group somehow]
FROM condities c 
GROUP BY c.attribute_id
ORDER BY c.execution_index asc, c.id asc

但我不知道如何让它真正起作用。

Answer 1

看起来您可以使用可更新的 CTE:

with cte as (
    select *, 
      Row_Number() over(partition by attribute_id order by execution_index, id) new
    from conditions
)
update cte set execution_index = new

我建议添加一个新列并首先更新它并检查结果是否符合预期。

Example Fiddle