Shulou(Shulou.com)06/01 Report--

This article mainly introduces the method of MySQL to delete duplicate data, the article is very detailed, has a certain reference value, interested friends must read it!

Recently, we are doing the function of importing a batch of data into the MySQL database. We can know from the batch import that such data will not be judged repeatedly before it is inserted into the database, so only after all the data is imported, a statement is executed to delete it to ensure the uniqueness of the data.

I won't say much. Let's take a look at the detailed introduction.

The table structure is shown in the following figure:

Table name: brand

Operation

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:

Summary:

A lot of things need to be explored step by step, of course, online suggestions are also very valuable reference and resources, no matter what development we need to understand its working principle in order to better master it.

The above is all the contents of MySQL's method of deleting duplicate data, thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.