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.

                                                                                                                The corresponding weight table corresponding to

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.

##idincrement sku_idsku_idIDweightWeight

Field	Description

Assume table name:

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 clause

The 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!