I mass import products into my site on a nightly basis (affiliate products). Occasionally the products are duplicated so I want to write a quick SQL Statement to remove the duplicated products.
What I will then do is put the SQL into a PHP script and call it each night after the import so it will remove the duplicated products before I reindex.
I've done some research and found come up with the following script to get all of the product names from the database:
SELECT `value` AS product_nameFROM catalog_product_entity_varchar WHERE entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product') AND attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product'))
I have also found this example script to identify duplicates in SQL:
`SELECT name, email, COUNT(*) FROM users GROUP BY name, email HAVING COUNT(*) >` 1
I wondered if someone could help me put this together?
Thanks in advance! Chris
SELECT value_id FROM catalog_product_entity_varchar WHERE entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product') AND attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product'))GROUP BY value HAVING COUNT(*) > 1
but can't figure out how to delete them?