Oracle uses rowid to delete duplicate records in a table

Oracle uses rowid to delete duplicate records in a table

 

First look at the table myemp

 

Identify records with duplicate data

 

Identify records with no duplicate data

 

Identify non-Repeated Records

 

Or

Select * From myemp e where rowid = (select max (rowid) from myemp E2 where E. userid = e2.userid and E. Username = e2.username and E. Salary = e2.salary)

 

 

How to delete duplicate data

1,
When a large amount of duplicate data exists and the column userid, username, and salary have indexes

Delete myemp where rowid not in (select max (rowid) from myemp group by userid, username, salary );

2. Suitable for the case of a small amount of duplicate data (when there is a large amount of data, the efficiency is very low)

Delete myemp e where rowid <> (select max (rowid) from myemp E2 where E. userid = e2.userid and E. Username = e2.username and E. Salary = e2.salary );

3. Exception method, suitable for the case of a large number of duplicated data

First, create an exception table.

 

Then add constraints to record errors to the table exceptions.

 

Create a temporary table for duplicate data

 

Delete all duplicate data

 

Re-insert non-duplicate data in the temporary table into the original table