Remove Products With Duplicate SKU From Magento2

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:

SELECT COUNT(*), sku
FROM `catalog_product_entity`
GROUP BY sku HAVING COUNT(*) > 1

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:

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

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:

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