Find and Remove Duplicates in MySQL

Once we faced a problem. We received a ticket from our client, “Can't update the price of the 63072 m item”. This is an e-commerce website with automatic data import in xls format. We built this site a long time ago.

Looking for the solution, I noticed that some items in the database are duplicated. I found out that there is no unique index in the product identification number column. But now we can't just put it there since we have duplicate records.

Solution:
Create a temporary table and write unique identification numbers of the items in it, then delete the duplicate records from the main table.

Here's the code.

CREATE TEMPORARY TABLE `p_temp`
  as  (
    SELECT min(product_id) as id
    FROM `wm_products`
    GROUP BY product_code
  );

DELETE from `wm_products`
WHERE `wm_products`.product_id not in (
  SELECT id FROM p_temp
);

ALTER TABLE `wm_products` ADD UNIQUE (
  `product_code`
);

In this way, I removed the unnecessary stuff from the table. Then I found a mistake in the script that imports the items and fixed it.

Hope the article will be useful for you.