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

hi,
 thanks for pointing out the email.
I have been trying to open the link and seems that
postgresql archive site is down.

would you please send me the content of the link that
you sent earlier. I appreciate your help.

thanks
sri

--- Richard Broersma Jr <rabroersma@yahoo.com> wrote:

> > 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.
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com