During database usage, due to program problems, duplicate data may occur, which leads to incorrect database settings ......
During database usage, due to program problems, duplicate data may occur, which leads to incorrect database settings ......
Method 1
The Code is 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 primary field = @ id
Fetch cur_rows into @ id, @ max
End
Close cur_rows
Set rowcount 0
Method 2
There are two Repeated Records. One is a completely repeated record, that is, records with all fields being repeated, and the other is records with duplicate key fields, such as duplicate Name fields, other fields are not necessarily repeated or can be ignored.
1. For the first type of repetition, it is easier to solve.
The Code is as follows:
Select distinct * from tableName
You can get the result set without repeated records.
If you want to delete a duplicate record (record:
Select distinct * into # Tmp from tableName
Drop table tableName
Select * into tableName from # Tmp
Drop table # Tmp
The reason for this repetition is that the table design is not weekly. You can add a unique index column.
2. Repeat problems usually require that the first record in the repeat record be retained. The procedure is as follows:
Assume that the duplicate fields are Name and Address. You must obtain the unique result set of the two fields.
The Code 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 command gets the result set with no duplicate Name and Address (but an autoID field is added, which can be omitted in the select clause when writing)
Some time ago, I wrote how to delete repeated records in the SQL database for the SQL database. Recently, I found that someone needs to access the database to delete Repeated Records and I will share them with you.
The Code is as follows:
Delete from table name where repeated field name in (select repeated field name from table name group by repeated field name having count (repeated field name)> 1) and id not in (select min (id) from table name group by repeated field name having count (repeated field name)> 1)
You can change the table name and repeated field name to your database name and field name.
