在复合唯一约束中重新编号键

Question

| id | sub_id | fk_id |
|----|--------|-------|
| 1  | 1      | 1     |
| 2  | 2      | 1     |
| 3  | 3      | 1     |
| 4  | 4      | 1     |
| 5  | 5      | 1     |
| 6  | 1      | 2     |
| 7  | 2      | 2     |
| 8  | 3      | 2     |
| 9  | 4      | 2     |
| 10 | 5      | 2     |

我该如何进行重新编号？我在想一种INSERT...SELECT查询，但我不知道它应该如何工作。

Answer 1

您可以使用以下查询为给定的fk_id重新编号行：

select t_renum.*, count(t_lower.id) as new_sub_id
from mytable t_renum
join mytable t_lower
    on  t_lower.fk_id = t_renum.fk_id
    and t_lower.id <= t_renum.id
where t_renum.fk_id = @renumber_fk_id
group by t_renum.id

可以将结果与原始表连接起来进行更新，如下所示：

sql prettyprint-override">update mytable t
join (
    select t_renum.*, count(t_lower.id) as new_sub_id
    from mytable t_renum
    join mytable t_lower
        on  t_lower.fk_id = t_renum.fk_id
        and t_lower.id <= t_renum.id
    where t_renum.fk_id = @renumber_fk_id
    group by t_renum.id 
) t_renum using (id)
set t.sub_id = t_renum.new_sub_id

SQLFiddle