使用PostgreSQL上的特定值更新所有受影響的行-有解無憂

我有下表的簡化版本。每行都有一個 item_order 值，對其 parent_id 進行磁區。

item_id	專案名稱	parent_id	item_order
523	魚	1	1
562	蟲	1	2
612	老鼠	1	3
251	起司	1	4
723	番茄醬	2	1
912	意大利面	2	2
52	籌碼	2	3

假設我想將 'mice' 的 'item_order' 值設定為 1。

UPDATE
    my_table 
SET
    item_order = 1
    WHERE item_id = 612;

我需要如下更新表格。（預期的變化被突出顯示。）

item_id	專案名稱	parent_id	item_order
612	老鼠	1	1
523	魚	1	1 → 2
562	蟲	1	2 → 3
251	起司	1	3 → 4
723	番茄醬	2	1
912	意大利面	2	2
52	籌碼	2	3

如何更新同一 parent_id 中所有受影響行的 item_order 值？

uj5u.com熱心網友回復：

您可以通過從要移動的 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 行受影響

select *
from my_table
order by parent_id, item_order

item_id	專案名稱	parent_id	item_order
612	老鼠	1	1
523	魚	1	2
562	蟲	1	3
251	起司	1	4
723	番茄醬	2	1
912	意大利面	2	2
52	籌碼	2	3

關于db<>fiddle 的演示在這里

uj5u.com熱心網友回復：

找到與的parent_id記錄item_id = 1，然后更新所有parent_id等于的記錄parent_id。

UPDATE my_table 
SET
   item_order = 1
WHERE parent_id = (SELECT parent_id FROM my_table WHERE item_id = 1);

uj5u.com熱心網友回復：

您想要移動排序串列中的專案。為此，您必須將專案的排序鍵更改為新位置，并將該位置的所有現有排序鍵加一。

set @item_id = 612;
set @item_order = 1;

UPDATE my_table 
SET item_order =
   case when item_id = @item_id then @item_order
        when item_order >= @item_order then item_order   1
        else item_order
   end
WHERE parent_id = (SELECT parent_id FROM my_table WHERE item_id = @item_id);

uj5u.com熱心網友回復：

我通過對 item_order 列使用小數解決了這個問題。

如果我嘗試將 612 的 item_order 設定為 1，我將這個新的 item_order 值遞減一個虛擬浮點數。

UPDATE
    my_table 
SET
    item_order = 1 - 0.1
    WHERE item_id = 612;

現在表格如下所示：

item_id	專案名稱	parent_id	item_order
612	老鼠	1	0.9
523	魚	1	1
562	蟲	1	2
251	起司	1	4
723	番茄醬	2	1
912	意大利面	2	2
52	籌碼	2	3

然后我遍歷具有相同 parent_id 的行并將它們的 item_order 設定為row_number()函式的值。

UPDATE 
    my_table
SET
    item_order = T2.row_number
FROM 
    (SELECT item_id, item_order, row_number()
        OVER (ORDER BY item_order)
        FROM my_table
        WHERE parent_id = (SELECT parent_id FROM my_table WHERE item_id = 612))
    AS T2
WHERE T2.item_id = my_table.item_id;

結果如下：

item_id	專案名稱	parent_id	item_order
612	老鼠	1	1
523	魚	1	2
562	蟲	1	3
251	起司	1	4
723	番茄醬	2	1
912	意大利面	2	2
52	籌碼	2	3

上面的這個例子只在給定值之前插入。必須將虛擬浮點數添加到 item_order 以便在給定值之后插入。另一個問題是同一父 id 中的所有行都會更新，無論它們是否需要更新。

db<>小提琴

轉載請註明出處，本文鏈接：https://www.uj5u.com/net/406868.html

標籤：

上一篇：在處理sql資料庫時，laravel查詢無法正常作業

下一篇：根據序列和模式對列進行分類