SQL statement for querying and deleting duplicate records1, look for redundant records in the table, duplicate records are based on a single field (ID) to determineselect * 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 recordsDELETE from table WHERE (ID) in (the Select ID from table GROUP by ID has COUNT (ID) > 1) and ROWID not in (select MIN (ROWID ) from table GROUP by ID have COUNT (*) > 1); 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 extra duplicate records (multiple fields) in the table, leaving only the record with ROWID minimumDelete 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 ( Select min (rowid) from table group by ID,SEQ have Count (*) >1)5. Find redundant duplicate records (multiple fields) in the table, not including the smallest ROWID recordsSELECT * 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 (select min (rowid) from table group by ID,SEQ have Count (*) >1)
Oracle Lookup Delete duplicate data
