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.

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)

错误代码： 1093You can't specify target table 'weight' for update in FROM clause

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)

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!