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

In this issue, the editor will bring you about how to delete ACCESS duplicate records in SQL. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

There are two sense of duplicate records, one is a completely duplicate record, that is, a record in which all fields are duplicated, and the other is a record in which some key fields are duplicated, such as the Name field, while other fields are not necessarily duplicated or can be ignored. 1. For the first kind of repetition, it is relatively easy to solve, and the result set without duplicate records can be obtained by using select distinct * from tableName. If the table needs to delete duplicate records (keep 1 duplicate record), you can delete it as follows

The code is as follows: select distinct * into # Tmp from tableName drop table tableName select * into tableName from # Tmp drop table # Tmp

The reason for this repetition is the poor design of the table, which can be solved by adding unique index columns.

2. This kind of duplicate problem usually requires the retention of the first record in the duplicate record. The operation method is as follows: assume that the duplicate field is Name,Address, and the copy code for the unique result set of these two fields is as follows: select identity (int,1,1) as autoID, * into # Tmp from tableName select min (autoID) as autoID into # Tmp2 from # Tmp group by Name,autoID select * from # Tmp where autoID in (select autoID from # tmp2)

The last select results in a result set in which Name,Address does not repeat (but with an extra autoID field, which can be omitted in the select clause when actually writing)

The above is how to delete ACCESS duplicate records in the SQL shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are 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.