Database Delete duplicate data

First, the resolution of the entity duplication in the database.

Entity repetition is a complete repetition: the two rows of records in the table are exactly the same. This kind of data duplication needs to delete a record, the workaround is relatively simple, the following:

Use SELECT DISTINCT * FROM tablename to 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.

Second, the workaround for repeating fields in the database.

Fields Repeat, this type of repetition usually requires that the first record in the duplicate record be retained by the following method:

method One: assume that there is a duplicate field name that requires the result set to be unique
Select Identity (int,1,1) as Autoid, * into #Tmp from TableName
Select min (autoid) as autoid into #Tmp2 from #Tmp Group by Name
SELECT * from #Tmp where autoid on (select Autoid from #tmp2)
The last select gives the name a result set that is not duplicated (but one more autoid field, which can be written in the SELECT clause when the actual write is omitted, if you want to delete the duplicate data in the library, as long as the corresponding delectable or drop off).

method Two: keep the latest record in the duplicate data, the following method:
In Oracle, ROWID is a hidden field used to uniquely identify each record. So, as long as you keep rowid the largest record in duplicate data.
To query for duplicate data:
Select a.rowid,a.* from table name a
where A.rowid! = (
Select Max (b.rowid) from table name B
Where a. Field 1 = B. Field 1 and a. Field 2 = B. field 2);
Example: Selete from Dba_tables a
Where a.rowid!= (
Select Max (ROWID) from Test b
where A.owner=b.owner);

method Three: delete duplicate data, keep only the latest data, the operation method is as follows:
Delete from table name a
where A.rowid! = (
Select Max (b.rowid) from table name B
Where a. Field 1 = B. Field 1 and a. Field 2 = B. Field 2)

Database Delete duplicate data