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

This article will explain in detail how to delete duplicate data in SQL Server. The content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Method 1 copy the code as follows: declare @ max integer,@id integer declare cur_rows cursor local for select main field, count (*) from table name group by main field having count (*) > 1 open cur_rows fetch cur_rows into @ id,@max while @ @ fetch_status=0 begin select @ max = @ max-1 set rowcount @ max delete from table name where main field = @ id fetch cur_rows into @ id,@max end close cur_rows set rowcount 0

Method 2 has two duplicate records, one is a completely duplicate record, that is, a record in which all fields are repeated, and the other is a record in which some key fields are repeated, such as Name fields, 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 the copy code 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 require the unique result set select identity (int,1,1) as autoID of these two fields. * 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 will get Name. Address does not repeat the result set (but there is an extra autoID field, which can be omitted in the select clause when actually writing)

On how to delete duplicate data in SQL Server to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.