Thread:

From
"Surabhi Ahuja "
Date:
i have a table in which duplicate rows occur.
 
now i have to remove the duplicates. Please note that however, only the duplicate rows have to be deleted and not the original one.
 
How do i do it?

Re:

From
Jonel Rienton
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi Surabhi, search the archives, this has been discussed quite a few
times in the pass.

regards,

- -----
Jonel Rienton
http://blogs.road14.com
Software Developer, *nix Advocate
On Feb 8, 2005, at 11:44 AM, Surabhi Ahuja wrote:

> i have a table in which duplicate rows occur.
>  
> now i have to remove the duplicates. Please note that however, only
> the duplicate rows have to be deleted and not the original one.
>  
> How do i do it?

-----BEGIN PGP SIGNATURE-----
Version: PGP 8.1

iQA/AwUBQgjvDeAwOVAnbsGCEQKiwgCgn0JzdJKYXMq3WgeskWTKmg6xCUMAnRz9
+gfpmg4HI+PZPMU+KQcKPuLY
=6189
-----END PGP SIGNATURE-----


Removing duplicates

From
Bruno Wolff III
Date:
Please use a relevant subject for your posts.

On Tue, Feb 08, 2005 at 23:14:57 +0530,
  Surabhi Ahuja  <surabhi.ahuja@iiitb.ac.in> wrote:
> i have a table in which duplicate rows occur.
>
> now i have to remove the duplicates. Please note that however, only the duplicate rows have to be deleted and not the
originalone. 
>
> How do i do it?

If the table has oids, then you can delete all but the row with the minimum
oid for each set of duplicates.

Another option is to do a select distinct into a temp table, a delete and
then copy the temp table back.

You should also add a constraint so that duplicates can't get back into
the table once you have it cleaned up.