CODEWITHSUNDEEP
phpmysqlopencart
Developer Gee
Developer Gee

Reputation: 402

MySQL find duplicate products and update column

I am working on a website using opencart and have it setup to automatically import products from two different wholesalers. The database contains about 40,000 different products in it, and about 10,000 of them are duplicates (both carry the product).

I need to lookup all duplicate products (by UPC) and enable the product with the lower price.

The table is called "product" and has the columns product_id, upc, price, status, and others that aren't relavent.

My first attempt was to run the following query

SELECT DISTINCT (
upc
) AS upcDuplicate, COUNT( upc ) AS upcCount
FROM product
GROUP BY upc
HAVING upcCount >1

which gave me a list of all duplicate products, then I was going to loop through those upcs in php and run

UPDATE product SET status=1 WHERE upc='CURRENTUPC' ORDER BY price ASC LIMIT 1

which would enable the one with the lowest price.

This works, but seems very inneficient seems how it is relying on php to run 10,000 different mysql queries. Is there any way to put this into a single query, or at least something more efficient.

Upvotes: 0

Views: 837

Answers (2)

Vivek
Vivek

Reputation: 1680

Instead of writing this in PHP, you can always create a procedure at the DB level and call it from your PHP code, which will at least save the conversation time (9999 in this case) between PHP and DB

Upvotes: 0

Marc B
Marc B

Reputation: 360652

Not tested, so it may kick your dog and steal your lunch money, but...

UPDATE product
INNER JOIN (
    SELECT upc, MIN(price) AS minprice
    FROM product
    GROUP BY upc
) AS derived_product
SET product.status=1
WHERE product.upc = derived_product.upc AND product.price=derived_product.minprice

Upvotes: 2

Related Questions