Find and Remove Duplicates in MySQL

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

Looking for the solution, I noticed that some items in the database are duplicated. So I understood 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`
);

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

Hope, the article will be useful for you.