This article will introduce to you how to delete duplicate data in MySQL. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to everyone.
SKU should correspond to the only weight data corresponding to the SKU_ID, which causes the same redundant data due to program errors. Only keep one and delete the others.
Field | Description |
---|---|
increment | |
sku_idID | |
Weight |
weight
Query a list with duplicate data
SELECT sku_id,COUNT(id) FROM weight GROUP BY sku_id HAVING COUNT(sku_id) > 1
Query duplicates For each smallest id in the data
SELECT min(id) FROM weight GROUP BY sku_id HAVING COUNT(sku_id) > 1
Query other data with the smallest id that removes duplicate data
SELECT id,sku_id FROM weight WHERE sku_id IN( SELECT sku_id FROM weight GROUP BY sku_id HAVING COUNT(sku_id) > 1)AND id NOT IN( SELECT MIN(id) FROM weight GROUP BY sku_id HAVING COUNT(sku_id) > 1)
Delete other data with the smallest id that removes duplicate data Data
DELETE FROM weight WHERE sku_id IN( SELECT sku_id FROM weight GROUP BY sku_id HAVING COUNT(sku_id) > 1)AND id NOT IN( SELECT MIN(id) FROM weight GROUP BY sku_id HAVING COUNT(sku_id) > 1)The reason is: while deleting this table, this table is queried at the same time, and this table is queried and deleted at the same time, which can be understood as a deadlock. Mysql does not support this operation of deleting and querying the same table
错误代码: 1093You can't specify target table 'weight' for update in FROM clauseThe solution is as follows: Query the data to be deleted as a third-party table, and then filter and delete it.
DELETE FROM `weight` WHERE sku_id IN( SELECT sku_id FROM (SELECT sku_id FROM `weight` GROUP BY sku_id HAVING COUNT(sku_id) > 1) table1)AND id NOT IN ( SELECT id FROM (SELECT MIN(id) AS id FROM `weight` GROUP BY sku_id HAVING COUNT(sku_id) > 1) table2)The update also operates on the same principle as above. Recommended learning: "
mysql video tutorial"
The above is the detailed content of Explain how mysql deletes duplicate data. For more information, please follow other related articles on the PHP Chinese website!