Subject [maybe OT] deleting duplicates
Author duilio_fos
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 ?


Duilio Foschi