Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to delete duplicate data in mysql". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to delete duplicate data in mysql".

For the weight scale corresponding to sku, a sku_id should correspond to a unique piece of weight data, and the same redundant data should occur due to program errors. Only one item is retained and the others are deleted.

Field description id self-increasing sku _ idsku_id logo weight weight

Suppose the table name is weight

Query lists with duplicate data

SELECT sku_id,COUNT (id) FROM weight GROUP BY sku_id HAVING COUNT (sku_id) > 1

Query each smallest id in the duplicated data

SELECT min (id) FROM weight GROUP BY sku_id HAVING COUNT (sku_id) > 1

The query removes other data with the minimum id of duplicated 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 that removes the minimum duplicated id

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: delete the table and query the table at the same time, query the table at the same time to delete the table, can be understood as a deadlock. Mysql does not support this operation of deleting and querying the same table

Error code: 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 the deletion.

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 works on the same principle as above.

Thank you for your reading, the above is the content of "how to delete duplicate data in mysql". After the study of this article, I believe you have a deeper understanding of how to delete duplicate data in mysql, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.