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. 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 Software Development Network www.mscto.com
|
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 tables
Insert [Table name]
Select * From temp2
|
6. delete a temporary table
Drop table temp1
Drop table temp2 Software Development Network www.mscto.com
|
Method 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 can only accommodate @ max-1 records. If there are ten duplicate records, the limit will be divided into 10 records, and one will definitely be left. 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.
As
Begin
Declare @ Max integer, @ ID varchar (30 ),
@ SQL varchar (7999), @ type Integer
Select @ 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_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
Endselect * From policypes
Select * 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)
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 ('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 ')
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 oninsert into DUP (ID, name)
Select ID, name
From tempdb .. wk
Go
Set identity_insert DUP off
Go
Select *
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.
Original article: http://www.mscto.com/SqlServer/031041606.html
