How to delete duplicate records in Oracle

The SQL statement used to delete record duplication is one of our most common statements. The following describes how to delete record duplication in Oracle. It is helpful to you.

1. Search for redundant duplicate records in the Table. duplicate records are determined based on the peopleId of a single field.
Select * from people
Where peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1)

2. Oracle deletes duplicate records. duplicate records are determined based on the peopleId of a single field. Only the records with the smallest rowid are retained.
Delete from people
Where peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1)
And rowid not in (select min (rowid) from people group by peopleId having count (peopleId)> 1)

Note: rowid is not required for oracle .....

Find multiple fields that are redundant in the Table)
Select * from vitae
Where (. peopleId,. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1) 4. Oracle deletes multiple fields of record duplication. Only records with the smallest rowid are retained.
Delete from vitae
Where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1)
And rowid not in (select min (rowid) from vitae group by peopleId, seq having count (*)> 1)

Searches for multiple fields with the least rowid.
Select * from vitae
Where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1)
And rowid not in (select min (rowid) from vitae group by peopleId, seq having count (*)> 1)

Implementation of oracle query current time

Learn more about Oracle hierarchical Query

Provides you with an in-depth understanding of Oracle temporary tables

Oracle statements used to query all tables of a user

Oracle with statement usage