How to query duplicate data in MySQL

Today, I will talk to you about how to query duplicate data in MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

The table structure is shown in the following figure:

Show: brand

Action:

Use SQL statements to query for duplicate data:

SELECT * from brand WHERE brandName IN (select brandName from brand GROUP BY brandName HAVING COUNT (brandName) > 1 # if the number of duplicates is greater than 1)

Use SQL to remove excess duplicate data and retain the smallest unique piece of data in Id:

Note:

Error 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)

Hint: You can't specify target table 'brand' for update in FROM clause cannot specify the target table "brand" for updates in the FROM clause

The reason is that we cannot take the data directly checked and dealt with as a condition for deleting data. We should first create a new temporary table for the checked data, and then delete the temporary table as a condition.

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) # query shows that the duplicated data shows the first few items, so there is no need to query whether the minimum value

The result is as follows:

After reading the above, do you have any further understanding of how to query duplicate data in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.