PL/SQL Delete duplicate records

--the database is used in the process due to procedural problems sometimes encounter duplicate data, duplicate data causes the database part of the settings are not set correctly ...

Method One

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 main field = @id
Fetch cur_rows into @id, @max
End
Close Cur_rows
SET ROWCOUNT 0

Method Two

There are two meanings of duplicate records, one is a completely duplicate record, that is, all fields are duplicated records, and the second is some key field duplicate records, such as the Name field repeats, and other fields may not be repeated or can be ignored.

1, for the first kind of repetition, easier to solve, using

SELECT DISTINCT * from TableName

You can get a result set with no duplicate records.

If the table needs to delete duplicate records (duplicate records retain 1), you can delete them as follows

SELECT DISTINCT * to #Tmp from TableName
drop table TableName
SELECT * Into TableName from #Tmp
drop table #Tmp

This duplication occurs because the table is poorly designed and the unique index columns are added to resolve.

2, this kind of repetition problem usually requires to keep the first record in the duplicate record, the operation method is as follows

Suppose there is a duplicate field name,address, which requires the result set to be unique for both fields

Select Identity (int,1,1) as Autoid, * into #Tmp from TableName
Select min (autoid) as autoid into #Tmp2 from #Tmp Group by name,address
SELECT * from #Tmp where autoid on (select Autoid from #tmp2)

The last select is the result set that name,address not duplicate (but one more autoid field, which can be written in the SELECT clause without this column in the actual write)

Before importing to the original data table, we need to delete the newly added autoid field column to ensure consistency of the field structure between the data tables.

Create an intermediate table Tmp3 and import the non-repeating result set from the last one above into Tmp3.

SELECT * into #Tmp3 from #Tmp where autoid in (select Autoid from #Tmp where autoid in (select Autoid from #tmp2))

In this way, the collection of records saved in Tmp3 is the result set that we want to get. But because autoid fields exist in Tmp3, we first delete the column in the Tmp3 table.

Alter TableName Drop Column autoid

We then took the first repetitive workaround to import the collection of records in Tmp3 into the data table we needed to import.

SELECT DISTINCT * to #Tmp3 from TableName
drop table TableName
SELECT * Into TableName from #Tmp3
drop table #Tmp3
drop table #Tmp2
drop table #Tmp

With the above steps, the TableName is the result set we want to get, there is no duplicate record

This article from "Lonely" blog, reproduced please contact the author!

PL/SQL Delete duplicate records