Re: Removing duplicate keys and updating deleted entry key in other table

> I have a table that has 19 duplicte entries:
> table - sequence:
>  seq_id |  seq_refseq  |    seq_gname
> --------+--------------+-----------------
>   33014 | NM_025196    | GRPEL1
>   33015 | NM_007186    | CEP2
>   33016 | NM_018148    | LINS1
>   33017 | NM_199166    | ALAS1
> .....
> I suspected there are some duplicate entries:
> >select seq_refseq,count(*) as score from sequence
> group by seq_refseq having count(*) > 1;
>   seq_refseq  | score
> --------------+-------
>  NM_033421    |     2
>  NM_018290    |     2
>  .....................
>  NM_005311    |     2
> (19 rows)
> > select * from sequence where
>   seq_refseq ='NM_033421';
>  seq_id | seq_refseq | seq_gname
> --------+------------+-----------
>   43535 | NM_033421  | C20orf161
>   43554 | NM_033421  | C20orf161
> (2 rows)
> Now that 19 records are duplicated, I want to delete
> the duplicated records from other table comb:
> Table comb:
> >select * from comb;
>   cid   |  gid  | seq_id
> --------+-------+--------
>   85830 |  5116 |  33014
>   85831 | 22191 |  33014
>   85832 | 22186 |  33014
>  .......................
> for some i checked to see if any records holds the
> duplicated key. i found none for 3 or 4 cases.
> select * from comb where comb.seq_id = 43539;
>  cid | gid | seq_id
> -----+-----+--------
> (0 rows)
> my question is how do I delete the duplicate row and
> make sure I update the comb table after I delete the
> duplicate key.
> I never did this before and is a complex problem for
> me to code.

I tried to come up with the sql on my own, but then I remembered a similar email that already had
a good answer.
http://archives.postgresql.org/pgsql-novice/2006-06/msg00092.php

Regards,

Richard Broersma Jr.