Sometimes It happens to have different product with same sku in magento2 when we migrate database from m1 to m2 OR third party modules used for bulk import. So when we try to save product which have repeated sku then it gives error like “The value of attribute “SKU” must be unique.”
Its hard to find products with repeated sku. So here i am explaining the process of how we can remove products with repeated SKU. The result will be like 1 product with same SKU will remain and other product will remove.
1) Backup your catalog_product_entity database table.
2) Check if duplicate skus are presents in catalog_product_entity table with this query:
| 1 2 3 | SELECT COUNT(*), sku FROM `catalog_product_entity` GROUP BY sku HAVING COUNT(*) > 1 |
3) Remove duplicate entries
Remove new duplicate products and keep older sku with:
| 1 2 3 4 5 6 7 8 | DELETE catalog_product_entity FROM catalog_product_entity LEFT OUTER JOIN ( SELECT MIN(entity_id) as id, sku FROM catalog_product_entity GROUP BY sku ) as t1 ON catalog_product_entity.entity_id = t1.id WHERE t1.id IS NULL |
To remove old duplicate products and keep new sku, replace MIN by MAX in join subquery:
| 1 2 3 4 5 6 7 | DELETE catalog_product_entity FROM catalog_product_entity LEFT OUTER JOIN ( SELECT MAX(entity_id) as id, sku FROM catalog_product_entity GROUP BY sku) as t1 ON catalog_product_entity.entity_id = t1.id WHERE t1.id IS NULL |
