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

This article will explain in detail how to delete duplicate data in SQLServer. 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 one

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 two

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. The copy code is as follows: select distinct * from tableName

You can get a result set with no duplicate records. 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 unique result set of these two fields is required.

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 gets the result set that Name,Address does not repeat (but there is an extra autoID field, which can be written in the select clause to omit this column). Some time ago, how to delete duplicate records in sql database is aimed at sql database. Recently, it was found that someone asked access database to delete duplicate records and send them to share with you.

The code is as follows:

Delete from table name where repeating field name in (select repeating field name from table name group by repeating field name having count (repeating field name) > 1) and id not in (select min (id) from table name group by repeating field name having count (repeating field name) > 1)

You can change the table name and duplicate field name to your database name and field name.

On how to delete duplicate data in SQLServer 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.