How to delete duplicate data in MySQL

Duplicate checking is a need we often encounter in our work. The following article mainly introduces you to the relevant information about MySQL querying duplicate data (deleting duplicate data and keeping the one with the smallest ID as the unique data). In the article The sample code is introduced in great detail and has certain reference value for everyone's study or work.

Recommended courses: MySQL Tutorial.

Recently I am working on a batch data import function into the MySQL database. From the batch import, we can know that such data will not be repeatedly judged before being inserted into the database. Therefore, only after all the data is imported, a statement can be executed to delete it to ensure the uniqueness of the data.

Not much to say below, let’s take a look at the detailed introduction

The table structure is as shown below:

Table Name: brand

Operation

Use SQL statements to query duplicate data:

SELECT * from brand WHERE brandName IN(
select brandName from brand GROUP BY brandName HAVING COUNT(brandName)>1 #条件是数量大于1的重复数据
)

Use SQL to delete redundant duplicate data and retain the unique piece of data with the smallest ID:

Note:

Wrong SQL ：DELETE FROM brand WHERE brandName IN (select brandName from brand GROUP BY brandName HAVING COUNT(brandName)>1)
AND Id NOT IN (select MIN(Id) from brand GROUP BY brandName HAVING COUNT(brandName)>1 )

Tip: You can't specify target table 'brand' for update in FROM clause You can't specify target table 'brand' for update in FROM clause

The reason is: the directly investigated data cannot be used as a condition for deleting data. We should first create a temporary table for the detected data, and then use the temporary table as a condition for deletion function

正确SQL写法：
 DELETE FROM brand WHERE brandName IN 
 (SELECT brandName FROM (SELECT brandName FROM brand GROUP BY brandName HAVING COUNT(brandName)>1) e)
 AND Id NOT IN (SELECT Id FROM (SELECT MIN(Id) AS Id FROM brand GROUP BY brandName HAVING COUNT(brandName)>1) t)
#查询显示重复的数据都是显示最前面的几条，因此不需要查询是否最小值

The results are as follows:

Summary:

Many things need to be done by yourself It is explored step by step. Of course, the suggestions on the Internet are also very valuable reference and resources. No matter what development we do, we need to understand its working principle in order to better master it.

The above is the detailed content of How to delete duplicate data in MySQL. For more information, please follow other related articles on the PHP Chinese website!