Home  >  Article  >  Database  >  Explain how mysql deletes duplicate data

Explain how mysql deletes duplicate data

藏色散人
藏色散人forward
2022-01-11 16:02:0610507browse

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.

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

Statement:
This article is reproduced at:learnku.com. If there is any infringement, please contact admin@php.cn delete