Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Removing duplicate records without using a pk
We have to change the unique index on a table that contains duplicate records. The new unique index is composed of c1, c2 and c3. I have written the following SQL to remove the duplicates. It works for most of the records but still leaves two records each with a duplicate. The first subquery should pick up all the duplicates and the second subquery should tell me which one of the dups not to delete. Does anyone know why it doesn't work? BTW, the table doesn't have a PK.
delete t1 where ((c1, c2, c3) in
(select c1, c2, c3 from t1 group by c1, c2, c3 having count(*) > 1))
and (rowid not in (select max(rowid) from t1 group by c1, c2, c3 having
count(*) > 1));
TIA Received on Wed Feb 21 2001 - 10:29:17 CST