Four Methods: how to use SQL statements to delete duplicate records

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 temp1
Select [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 temp1
Drop table temp2

Solution 2:

Declare @ Max integer, @ ID integer
Declare cur_rows cursor local
Select ID, count (*) from table name group by ID 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 id = @ ID
Fetch cur_rows into @ ID, @ Max
End
Close cur_rows
Set 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.

Solution 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_dist

Create procedure up_distinct (@ t_name varchar (30)
, @ f_key varchar (30 ))
-- f_key indicates the grouping field, that is, the primary key field
as
begin
declare @ Max integer, @ ID varchar (30 ),
@ SQL varchar (7999), @ type Integer
select @ SQL = 'destare 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_key
If @ 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_rows
set rowcount 0
end

Select * From policypes
Select * From syscolumns where
Id = object_id ('A _ dist ')
Solution 4:

You can use ignore_dup_key:

Create Table DUP (ID int identity not null,
name varchar (50) not null)
go
insert into DUP (name) values ('abc')
insert into DUP (name) values ('abc ')
insert into DUP (name) values ('abc')
insert into DUP (name) values ('abc')
insert into DUP (name) values ('cdef ')
insert into DUP (name) values ('xyz ')
go
select *
from DUP
go
Create Table tempdb .. WK (ID int not null,
name varchar (50) not null)
go
create unique index idx_remove_dup
On tempdb .. WK (name)
with ignore_dup_key
go
insert into tempdb .. WK (ID, name)
select ID, name
from DUP
go
select *
from tempdb .. WK
go
Delete from DUP
go
set identity_insert DUP on

Insert into DUP (ID, name)
Select ID, name
From tempdb .. wk
Go
Set identity_insert DUP off
Go
Select *
From DUP

Go

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

**************************************** ***********************************

Select distinct * into # T from tablename

Truncate table tablename

Insert tablename select * from # T

Drop table # T