Four Methods for deleting duplicate records using SQL statements _ MySQL

There are four ways to delete duplicate records using SQL statements: How to delete records with the same fields 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.

Use SQL statements to delete duplicate records:

Method 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 the table
Insert [table name] select * from temp2

6. delete a temporary table
Drop table temp1drop table temp2


Method 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. if there are ten duplicate records, delete 10 records and leave one. You can also write the delete from table name.

Method 3:

Create table a_dist (id int, name varchar (20) insert into a_dist values (1, 'ABC') insert into a_dist values (1, 'ABC ') insert into a_dist values (1, 'ABC') insert into a_dist values (1, 'ABC') exec up_distinct 'a _ dist ', 'id' select * from a_distcreate procedure up_distinct (@ t_name varchar (30), @ f_key varchar (30) -- f_key indicates the group field, that is, the primary key field asbegindeclare @ max integer, @ id varchar (30), @ SQL varchar (7999), @ type integerselect @ SQL = 'Clare cur_rows cursor for Select' + @ f_key + ', count (*) from '+ @ t_name + 'group by' + @ f_key + 'having count (*)> 1' exec (@ SQL) open cur_rows fetch cur_rows into @ id, @ max while @ fetch_status = 0 begin select @ max = @ max-1 set rowcount @ max select @ type = xtype from syscolumns where id = object_id (@ t_name) and name = @ f_keyif @ type = 56 select @ SQL = 'delete from' + @ t_name + 'where' + @ f_key + '=' + @ id if @ type = 167 select @ SQL = 'delete from' + @ t_name + 'where' + @ f_key + '=' + ''' + @ id + ''' exec (@ SQL) fetch cur_rows into @ id, @ max end close cur_rows deallocate cur_rowsset rowcount 0 endselect * from policypesselect * from syscolumns where id = object_id ('a _ dist ')

Method 4:
You can use IGNORE_DUP_KEY: create table dup (id int identity not null, name varchar (50) not null) goinsert into dup (name) values ('ABC') insert into dup (name) values ('ABC') insert into dup (name) values ('ABC') insert into dup (name) values ('cdefg') insert into dup (name) values ('XYZ') goselect * from dupgocreate table tempdb .. wk (id int not null, name varchar (50) not null) gocreate unique index idx_remove_dup on tempdb .. wk (name) with IGNORE_DUP_KEY goINSERT INTO tempdb .. wk (id, name) select id, namefrom dupgoselect * from tempdb .. wkgodelete from dupgoset identity_insert dup onINSERT INTO dup (id, name) select id, namefrom tempdb .. wkgoset identity_insert dup offgoselect * from dupgo


Note: delete the original table and add non-repeated values. You can also use join to delete duplicate values in the original table only.