1, look for redundant records in the table, duplicate records are based on a single field (ID) to determine
SELECT * FROM table where Id in (select ID from table group Byid have count (Id) > 1)
2, delete redundant records in the table, duplicate records are based on a single field (ID) to judge, leaving only the smallest ROWID records
DELETE from Table WHERE (judging field) in (Select Judgment field from Table GROUP by Judgment Field having COUNT (judging field) > 1) and ROWID not in (select MIN (R OWID) from table GROUP by ID have COUNT (*) > 1);
Note: The sentence after and is obtained by removing all records that have the smallest rowid (a single, non-repeating record is also included), where and between is getting all fields that contain duplicate records.
3. Find redundant duplicate records (multiple fields) in the table
SELECT * FROM Table a WHERE (A.ID,A.SEQ) in (Select Id,seq from table group by ID,SEQ have count (*) > 1)
4. Delete Redundant records (multiple fields) in the table, leaving only the smallest record of rowid (same principle as 2)
Delete from Table A where (A.ID,A.SEQ) in (Select Id,seq from table group by ID,SEQ have count (*) > 1) and rowID not in (Sele CT min (rowid) from table group by ID,SEQ have Count (*) >1)
Example two: Delete from student where s_id not in (select min (s_id) from student group by s_name,c_id);
5. Find redundant duplicate records (multiple fields) in the table, not including the smallest ROWID records
SELECT * FROM Table a WHERE (A.ID,A.SEQ) in (Select Id,seq from table group by ID,SEQ have count (*) > 1) and rowID not in (SE Lect min (rowid) from table group by ID,SEQ have Count (*) >1)
Note: Oracle queries duplicate data and delete, keeping only one record