MySQL - Find Duplicate Records

Recently I was working with the Magento API uploading products for a test project. The type of products that I was uploading where just simple types. Each product however required that it have a unique “SKU” code to represent it, and no duplicate records.

The data that I was given, some 19k products. All had “SKU” codes with them. So I went about uploading these via the API. Some time into the process I started to receive an error message back saying “SKU must be unique”

Now with 19k products and the possibility of there just being even one product with a duplicate SKU, there is only one action to take. Get stuck into the database with raw MySQL commands!

Here is the MySQL command to find duplicate records from a MySQL database.

SELECT sku
FROM product
GROUP BY sku
HAVING count(sku) > 1

This command will find all the SKU that are used more than once. You only get a single record for each SKU. The example output of which could be:

0001     Product 1
0002     Product 2
0003     Product 3

You could use this command in a where clause to get all rows based on the duplicated SKU:

SELECT *
FROM product
WHERE sku IN (
     SELECT sku
     FROM product
     GROUP BY sku
     HAVING count(sku) > 1
     )
ORDER BY sku

This command would then give you the following example output:

0001     Product 1
0001     Product 1
0002     Product 2
0002     Product 2
0003     Product 3
0003     Product 3

This MySQL command can be used for any single table. simply change the column & table name for that of your own and away you go!