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.
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.