postgresql query to delete duplicate entries in a table

Question

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.

click here to find the answer : http://stackoverflow.com/questions/1746213/how-to-delete-duplicate-entries — khelili miliana, May 05 '16 at 09:13
Of course your query doesn't work. There is no column `salary` in your table. Please show us the _real_ query that you are using. You also didn't tell us what the unique key for the table is supposed to be. It _looks_ like `(product_id, merchant_id, upc)`. Also do you have any **really** unique column in that table? The `id` column contains duplicates as well and is not suitable to distinguish the rows. — , May 05 '16 at 09:22
"No success" is not a description of what happened when you tried that query. Did you get a syntax error because you used a space instead of an underscore(`_`)? Did you get another type of error? Did you get output? You should post what actually happened, and if there was no error, explain how that output is different from what you want. — Dan Getz, May 05 '16 at 14:22

score 1 · Accepted Answer · answered May 05 '16 at 09:56

1

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

answered May 05 '16 at 09:56

Can u tell me how can I do the same in django orm? – the_unknown_spirit May 05 '16 at 11:18

postgresql query to delete duplicate entries in a table

1 Answers1