刪除重複記錄並更新映射表

我有 store_table_map 和 item_price_map。我想刪除 store_table_map 中的重複記錄並保留基於 updated_date 的最新行，因為我想在 store_id 和 item_id 中放置約束。但我還想先更新 item_price_map，如下所示，然後再刪除舊的重複記錄。

目前表

預期結果

這是腳本

CREATE TABLE store_table_map (
   map_id bigserial NOT NULL,
   store_id int8 NOT NULL,
   item_id int8 NOT NULL,
   updated_date date NOT NULL DEFAULT now()
);

INSERT INTO store_table_map (store_id,item_id,updated_date) VALUES 
(1,123,'2020-07-09')
,(1,123,'2020-07-10')
,(2,456,'2020-07-08')
,(2,456,'2020-07-10')
;

CREATE TABLE item_price_table_map (
   map_id bigserial NOT NULL,
   store_table_map_id int8 NOT NULL,
   price float8 NOT NULL,
   updated_date date NOT NULL DEFAULT now()
);

INSERT INTO item_price_table_map (store_table_map_id,price,updated_date) VALUES 
(1,99.0,'2020-07-09')
,(2,199.0,'2020-07-10')
,(3,299.0,'2020-07-08')
,(4,399.0,'2020-07-10')
;

postgres 版本：PostgreSQL 9.2.24

您首先需要創建從舊 map_id 到新 map_id 的映射，這可以使用distinct on()自連接來完成：

select m1.map_id old_map_id, m2.new_map_id
from store_table_map m1
 join (
   select distinct on (store_id, item_id) store_id, item_id, map_id as new_map_id
   from store_table_map 
   order by store_id, item_id, updated_date desc
 ) m2 on (m1.store_id, m1.item_id) = (m2.store_id, m2.item_id)

以上返回：

old_map_id | new_map_id
-----------+-----------
        1 |          2
        2 |          2
        3 |          4
        4 |          4

這可以在 UPDATE 語句中使用：

update item_price_table_map ip
 set store_table_map_id = t.new_map_id
from (  
 select m1.map_id old_map_id, m2.new_map_id
 from store_table_map m1
   join (
     select distinct on (store_id, item_id) store_id, item_id, map_id as new_map_id
     from store_table_map 
     order by store_id, item_id, updated_date desc
   ) m2 on (m1.store_id, m1.item_id) = (m2.store_id, m2.item_id)
) t 
where t.old_map_id = ip.store_table_map_id
 and ip.store_table_map_id <> t.new_map_id;

之後，您可以使用現在未使用的 map_ids 刪除行

delete from store_table_map stm
where not exists (select *
                 from item_price_table_map ip
                 where ip.store_table_map_id = stm.map_id);

（我只能在 Postgres 12 上測試這個，但我認為它也應該在 9.2 上工作）

引用自：https://dba.stackexchange.com/questions/270712