I have store_table_map and item_price_map. I want to delete duplicate records in store_table_map and retain the latest row based on updated_date because i want to put constraints in store_id and item_id. But I also want first to update the item_price_map as seen below before deleting old duplicate records.
Current Table
Expected Result
Here's the script
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 version: PostgreSQL 9.2.24
Best Answer
You first need to create the mapping from the old map_id to the new one, this can be done using
distinct on()
and a self join:The above returns:
This can be used in an UPDATE statement:
After that you can remove the rows with the now unused map_ids
(I could only test this on Postgres 12, but I think it should work on 9.2 too)