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

This article is about how to delete completely duplicated data in mysql. I think it is very practical, so I share it with you. I hope you can get something after reading this article. Let's take a look at it.

Case study:

Id name course name score

1 Zhang San Mathematics 69

2 Li Si Mathematics 89

3 Zhang San Mathematics 69

Delete redundant student information that is the same except for automatic numbering.

-

In common sense, this sql statement should be:

Delete tablename where id not in (select min (id) from tablename group by name,kecheng,fenshu)

This method of writing is supported in sqlserver or oracle, but is not currently supported, and will report a similar error: You can't specify target table 'tablename' for update, because you cannot query the data of a table and delete it at the same time in mysql.

At present, a popular solution on the Internet is:

1) create a temporary table and store the columns to be queried in the temporary table

Create table temp as select...

2) operate in temp table and original table

Delete from tablename

3) drop temp...

However, this approach is not only a waste of space resources, but also lack of friendliness. Through observation, we find that the problem of this kind of query is how to distinguish the table in the subquery from the table in the main query, so we can consider using the alias method to put the results of the subquery into an alias.

The complete sql statement is as follows:

DELETE FROM tablename where id not in (select bid from (select min (id) as bid from tablename group by name,kecheng,fenshu) as b)

Explanation:

Select bid from (select min (id) as bid from tablename group by name,kecheng,fenshu) as b

The purpose of this subquery is to list the filter results from b, that is, the collection of bid.

(select min (id) as bid from tablename group by name,kecheng,fenshu) as b

Treat the smallest bid in the grouping result as a collection of hearts as a child table b of a heart

Note that mid (id) must have an alias, here bid is used as a column name for b, because this column name is used in the upper-level query (marked in red).

The above is how to delete completely duplicated data in mysql. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please 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.