hello group:

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.


could any one please help me.

thanks
sri

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

> 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...6/msg00092.php

Regards,

Richard Broersma Jr.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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...6/msg00092.php
>
>
> Regards,
>
> Richard Broersma Jr.
>


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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

fixed now:

* From: <kynn ( at ) panix ( dot ) com>
* To: pgsql-novice ( at ) postgresql ( dot ) org
* Subject: SQL for removing duplicates?
* Date: Tue, 13 Jun 2006 12:11:41 -0400 (EDT)


Hi. I'm stumped. I have a large table (about 8.5M records), let's
call it t, whose columns include x and y. I want to remove records
from this table so that any pair of values for these two fields appear
only once. (This will get rid of about 15% of the records in t.)

One simple solution would be something like

CREATE TABLE tmp AS SELECT DISTINCT ON ( x, y ) * FROM t;
DROP TABLE t;
ALTER TABLE tmp RENAME TO t;

This works, but it uses a lot of space. I would prefer to simply cull
the unwanted records from t, but I just can't figure out the SQL for
it. Any help with it would be *much* appreciated.

Thanks!




On Wed, Sep 06, 2006 at 08:00:54AM -0700, Richard Broersma Jr wrote:
> > I have been trying to open the link and seems that
> > postgresql archive site is down.
> > http://archives.postgresql.org/pgsql...6/msg00092.php
>
> Sorry, the link didn't work. It worked yesterday when I found it.
>
> I will also forward this to the general list maybe someone there could take a look at it.
>
> Regards,
>
> Richard Broersma Jr.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

--

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings