How to remove a duplicate sku via database?

Question

I get this error when attempting to edit and save a few of my Magento products via admin: "The value of attribute "SKU" must be unique."

When I view my products in Magento admin a duplicate sku is nowhere to be found, but when I run a product export there are in fact some products with the same sku. In theory Magento should never have allowed this to happen, but alas, I'm dealing with a fairly large and mature (3+ year old) group of products that seems to carry the remnants of previous Magento bugs and quirks.

I'm guessing the only way to fix this issue is to manually remove one of the duplicate skus from the database, but I'm not sure of the best/safest way to do this. I have pretty much 0 experience working with databases, so any help is appreciated.

Answer 1

1. Backup your catalog_product_entity database table (see: https://stackoverflow.com/a/6683000/4457531)

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
   

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
     

   • To remove old duplicate products and keep new sku, replace MIN by MAX in join subquery

     LEFT OUTER JOIN (
         SELECT MAX(entity_id) as id, sku
         ...
     )
     

Answer 2

You probably don't have duplicate SKU's in your database, they only appear in magento 1.9.2.x when you run a export.

Try this:

https://github.com/baconl/Magento-1.9.2.1-duplicate-sku-fix

Its a temporary fix until the magento core team fixes it,

Answer 3

You ll need to investigate and clean those unexpected skus. First find the skus concerned, then you ll need to clean it in database with the query below:

DELETE FROM `catalog_product_entity` WHERE `sku` IN ('SKU1', 'SKU2', ..., 'SKU1654');

note that I recommend you to do it if you are sure of what you do, this will not be reversible.

Answer 4

Please run bellow script via database

DELETE FROM `catalog_product_entity` WHERE `entity_id` IN (SELECT *  FROM (SELECT `entity_id` FROM `catalog_product_entity` GROUP BY `sku` HAVING (COUNT(*) > 1)) AS A);