Re: Removing duplicate records without using a pk

I found out the why by myself. The c2 column contained NULL values and as such it could not be joined.

"NoSpam" <NoSpam_at_NoSpam.com> wrote in message news:970ipc$3rb$1_at_ih292.ea.unisys.com...
> 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 - 14:50:51 CST