MySQL query duplicate data (delete duplicate data and keep the one with the smallest id as the only data)

Development Background:

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

Let’s take a look at the detailed introduction.

Actual combat:

The table structure is shown in the following figure:

Indicates: brand

operate:

Use SQL statements to query what duplicate data there are:

SELECT * from brand WHERE brandName IN (
select brandName from brand GROUP BY brandName HAVING COUNT(brandName)>1 #The condition is that the number of duplicate data is greater than 1)

Use SQL to delete redundant duplicate data and keep the unique 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

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

Correct SQL writing:
 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)

#Querying for duplicate data only displays the first few items, so there is no need to query whether it is the minimum value

The results are as follows:

Summarize:

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

Well, the above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.