[Recommended] how to delete duplicate records using SQL statements

This article uses four methods to teach you how to use SQL statements to delete duplicate records.

Question: How to delete a record with the same field and leave only one record.

For example, the table test contains the id and name fields. If there are records with the same name, only one record is left, and the remaining records are deleted. The content of the name is not fixed, and the number of identical records is not fixed.

Solution 1:

1. Record Repeated Records in Table temp1:

Select [flag field id], count (*) into temp1 from [Table name] group by [flag field id] having count (*)> 1

2. Record non-Repeated Records in Table temp1:

Insert temp1select [flag field id], count (*) from [Table name] group by [flag field id] having count (*) = 1

3. Create a table that contains all non-Repeated Records:

Select * into temp2 from [Table name] where flag field id in (select flag field id from temp1)

4. delete duplicate tables: delete [Table name]

5. Restore table:

Insert [Table name] select * from temp2

6. delete a temporary table:

drop table temp1drop table temp2

Solution 2:

Declare @ max integer, @ id integerdeclare cur_rows cursor local for select id, count (*) from table name group by id having count (*)> 1 open cur_rowsfetch cur_rows into @ id, @ maxwhile @ fetch_status = 0 beginselect @ max = @ max-1 set rowcount @ maxdelete from table name where id = @ idfetch cur_rows into @ id, @ maxendclose cur_rowsset rowcount 0

Note: set rowcount @ max-1 indicates that the current buffer only contains @ max-1 records.

10, one will definitely be left. You can also write the delete from table name.

1 2 3 Next Page

[Content navigation]
Page 1: How to delete duplicate records using SQL statements	Page 1: solution 3:
Page 4: solution 4