Home >Database >Mysql Tutorial >Explain how mysql deletes duplicate data
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!