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.
