sql - 在 PostgreSQL 上使用特定值更新所有受影响的行

Question

我有下表的简化版本。每行都有一个按其parent_id 分区的item_order 值。

<表类=“s-表”><标题>item_id项目名称parent_iditem_order <正文>523鱼11562蠕虫12612老鼠13251奶酪14723番茄酱21912面食2252芯片23

假设我想将“mice”的“item_order”值设置为 1。

UPDATE
    my_table 
SET
    item_order = 1
    WHERE item_id = 612;

我需要更新表格如下。(突出显示了预期的更改。)

<表类=“s-表”><标题>item_id项目名称parent_iditem_order <正文>612老鼠13 → 1523鱼11 → 2562蠕虫12 → 3251奶酪14723番茄酱21912面食2252芯片23

如何更新同一 Parent_id 内所有受影响行的 item_order 值？

Answer 1

您可以通过更新从要移动的 item_id 获取 item_order 来完成此操作。

只有低于已更改订单的订单也需要移动。

UPDATE my_table t
SET item_order =
     case 
     when t.item_order = t2.item_order then 1
     when t.item_order < t2.item_order then t.item_order + 1
     else t.item_order
     end
FROM my_table t2
WHERE t.parent_id = t2.parent_id
  AND t.item_order <= t2.item_order
  AND t2.item_id = 612;

3 rows affected

select *
from my_table
order by parent_id, item_order

<表类=“s-表”><标题>item_iditem_nameparent_iditem_order <正文>612鼠标11523鱼12562蠕虫13251奶酪14723番茄酱21912意大利面2252芯片23

db<>fiddle 上的演示 here