Remove duplicate product with lower price using mysql query

Question

I have many duplicate products that I need to delete. Is there a query to delete these "Duplicates" with the "lower price" and/or "Same Price" an just keep 1 of each product?

The duplicates have duplicate "Product Name" I am using Opencart Version 2.1.0.1

Provide the table schema (which fields, keys, etc). Without that it is hard to give you a solution. — Nick, Feb 13 '16 at 07:59
Possibly duplicated with question [http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows](http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows) — RafaelCaballero, Feb 13 '16 at 08:30

geco17 · Answer 1 · 2016-02-13T09:11:14.690

1

Since you are using mysql, you need to use joins (partition by is not supported):

The select:

select p.* 
from products as p 
join 
(
    select name, min(price) as price 
    from products group by name having count(price) = 2
) as p2 on p2.name = p.name and p2.price = p.price;

Gets the lowest price for all duplicate products (where duplicate assumes there are exactly two rows of the same product).

To delete, change the initial select to a delete, as follows:

delete p.* 
from products as p 
join 
(
    select name, min(price) as price 
    from products group by name having count(price) = 2
) as p2 on p2.name = p.name and p2.price = p.price;

edited Feb 13 '16 at 09:11

answered Feb 13 '16 at 09:04

geco17

5,152
3
21
38

You could "reverse" this and get the MAX instead of MIN. Then only delete those that are not MAX. This would handle the cases where there are triplicates, etc. – Nick Feb 13 '16 at 09:11
The above query will work fine with duplicates that have either the same price or a different price. Using max and joining again, on same name and necessarily different price for the join / delete to keep the set of good values from the "reversed" query, the query no longer successfully removes duplicates (or multiples) that have the same price. – geco17 Feb 13 '16 at 09:29

score 0 · Answer 2 · answered Feb 13 '16 at 08:45

Without much info (which RDBMS, etc) I can only speculate this solution would work for you:

WITH Flagged AS
(
SELECT
    ProductName,
    CASE WHEN ROW_NUMBER() OVER(PARTITION BY ProductName ORDER BY Price DESC) = 1 THEN 0 ELSE 1 END AS Delete
FROM
    Products
)

DELETE p
FROM Products p
JOIN Flagged f
ON (p.ProductName = f.ProductName)
WHERE f.Delete = 1;

score 0 · Accepted Answer · answered Feb 13 '16 at 20:08

According to the schema on http://wiki.opencarthelp.com/doku.php?id=databse_schema and only one language the folowing query should solve your issue:

delete p1 
from product p1
join product_description d1 on d1.product_id = p1.product_id
join product_description d2 
  on  d2.product_id <> d1.product_id
  and d2.language_id = d1.language_id
  and d2.name = d1.name
join product p2 on p2.product_id = d2.product_id
where d1.language_id = 1 -- define the language used for product name
  and (p2.price > p1.price -- delete if higher price exists
    or p2.price = p1.price and p2.product_id < p1.product_id -- delete if same price with lower id exists
  )
;

Remove duplicate product with lower price using mysql query

3 Answers3