Oracle deletes duplicate data and keeps the first record

oracle deletes duplicate data and retains the first record

1. Find the redundant duplicate records in the table. Duplicate records are based on a single field ( Id) to judge

select * from 表 where Id in (select Id from 表 group byId having count(Id) > 1)

2. Delete redundant duplicate records in the table. Duplicate records are judged based on a single field (Id), leaving only the record with the smallest rowid

DELETE from 表 WHERE (id) IN ( SELECT id FROM 表 GROUP BY id HAVING COUNT(id) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM 表 GROUP BY id HAVING COUNT(*) > 1);

3. Search Redundant duplicate records (multiple fields) in the table

select * from 表 a where (a.Id,a.seq) in(select Id,seq from 表 group by Id,seq having count(*) > 1)

4. Delete redundant duplicate records (multiple fields) in the table, leaving only the record with the smallest rowid

delete from 表 a where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)

5. Search The redundant duplicate records (multiple fields) in the table do not include the record with the smallest rowid

select * from 表 a where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)

PHP Chinese website, there are a large number of free SQL tutorials, everyone is welcome to learn!

The above is the detailed content of Oracle deletes duplicate data and keeps the first record. For more information, please follow other related articles on the PHP Chinese website!