SQL statement for Oracle query and deletion of duplicate records 1. query for redundant duplicate records in the Table. duplicate records are determined based on a single field (Id). select * from Table whereI
SQL statement for Oracle query and deletion of duplicate records 1. query for redundant duplicate records in the Table. duplicate records are determined based on a single field (Id). select * from table where I
SQL statements used to query and delete duplicate records in Oracle
1. Search for redundant duplicate records in the Table. duplicate records are determined based on a single field (Id ).
Select * from table where Id in (select Id from Table group byId having count (Id)> 1)
2. Delete unnecessary duplicate records in the Table. duplicate records are determined based on a single field (Id), leaving only the records with the smallest rowid
DELETE from table WHERE (id) IN (SELECT id FROM Table group by id having count (id)> 1) and rowid not in (select min (ROWID) FROM table group by id having count (*)> 1 );
3. Search for redundant duplicate records in the table (multiple fields)
Select * from Table a where (a. Id, a. seq) in (select Id, seq from Table group by Id, seq having count (*)> 1)
4. Delete redundant record (multiple fields) in the table, leaving only the records with the smallest rowid
Delete from Table a where (. id,. seq) in (select Id, seq from Table group by Id, seq having count (*)> 1) and rowid not in (select min (rowid) from Table group by Id, seq having count (*)> 1)
5. Search for redundant duplicate records (multiple fields) in the table, excluding records with the smallest rowid
Select * from Table a where (. id,. seq) in (select Id, seq from Table group by Id, seq having count (*)> 1) and rowid not in (select min (rowid) from Table group by Id, seq having count (*)> 1)