the flash the flash - 6 months ago 24
SQL Question

postgresql query to delete duplicate entries in a table

I have a table as :

id product id merchant id price upc
1 124 2 2000000 1234XDE
2 124 2 200000 1234XDE
3 124 2 200000 1234XDE
4 124 2 200000 1234XDE
5 124 2 200000 ASDER36
6 134 1 300 ASERT56
7 134 2 300 ASERT56


I want to delete all the multiple entries from the table.

Delete from
table where id not in (Select min(id) from table group by(merchant id))


but no success. I want resulting table as:

id product id merchant id price upc
1 124 2 2000000 1234XDE
5 124 2 2000000 ASDER36
6 134 1 300 ASERT56
7 134 2 300 ASERT56


Can someone help me in writing a query for this.

Answer

This should do it:

delete from flash
where id not in (select min(id)
                 from flash
                 group by product_id, merchant_id, upc);

SQLFiddle example: http://sqlfiddle.com/#!15/9edef/1

Comments