Hi,

> Due to a program error, I have duplicated rows in table T that have
> the same value in Column1, while it should be unique.
>
> (OK: I should have declared a UNIQUE constraint, but let's skip the
> matter now).
>
> If N is the number of rows that share the same value in Column1, I
> need to delete N-1 of them.
>
> Column2 is unique for every row in T.
>
> If I write
>
> select * from T a where (select count(*) from T b where
> a.Column1=b.Column2)>1 order by Column2
>
> I can see the rows I want to delete, but I want to delete all
> duplicates except one.
>
> I can do it programmatically, but I guess that there must be a way to
> do the trick in SQL, even if I don't see it.
>
> Any hint ?

Test inside a transaction so you can rollback !!

DELETE FROM
TableA t1
WHERE
NOT SINGULAR(SELECT * FROM TableA t2 WHERE t2.DUB_KEYS = t1.DUB_KEYS)

Regards,
Arno Brinkman