Sometimes, it happens to have different products with the same SKU in magento 2 when we migrate the database from m1 to m2 OR third-party modules used for bulk import. So when we try to save a product that has repeated SKU, it gives an error like “The value of attribute “SKU” must be unique.”
It’s hard to find products with repeated SKUs. 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) Back up 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 |
