Re: Problems with UPDATE - Mailing list pgsql-general

orf is a primary key.  It is a unique string that links the tables.

Essentially, the setup is this: I am presented with data called
transcription profiles.  These contain two columns: orf, which is a unique
representation of a biological gene, and induction, which is a number.
Now, unfortunately, these profiles do not contain all of the known orfs,
and also they can sometimes contain orfs that do not exist.  so, when I
want to load in a profile to my database, i need to select all the
inductions from temp whose orfs exist in the master table.  This master
table, yeast1, is very big--perhaps 600 columns.  Basically, I'm just
adding columns to the database, and I'm looping it in Perl DBI.  I
wondered if there was a fast batch way to do that.

On a related note, I had another question for everyone: I need to do
calculations with these induction numbers--I need to select the entire
table of inductions, load them into a matrix, and pass them to a
computational C program through perl/cgi.  This step is obviously quite
rate-limiting, as it takes a very long time to select all from such a
large table (yeast1).  perhaps my database design is a little messed
up--basically, I'm hoping someone will tell me the best way to load,
store, and access this kind of data.

Hope this clarifies!

Thanks everyone,
Evan



On Mon, 20 Aug 2001, Andrew Gould wrote:

> If orf is not associated with a primary key or unique
> index, the possible combinations might well exceed
> 14,000.  For example, if 3 records in yeast1 and 4
> records in temp share the same orf value, the query
> will update yeast1 12 times (i.e. the 3 yeast1 records
> will be updated 4 times each).
>
> Can you clarify (for the server or for us) the
> relationship between the tables?  What primary keys or
> indexes do these tables have?
>
> Andrew
>
> --- Evan Zane Macosko <macosko@fas.harvard.edu> wrote:
> > Hello everyone,
> >
> >
> > I am having a strange problem using the UPDATE
> > function in Postgresql.  I
> > want to move a large number (6000) of entries in one
> > table to another, so
> > here is my syntax:
> >
> > UPDATE yeast1 SET c1 = temp.profile_1 FROM temp
> > WHERE yeast1.orf =
> > temp.orf;
> >
> > This query results in over 14000 entries being
> > added!  I end up with
> > multiples of the same entry, which obviously is
> > undesirable.  Does anyone
> > have any suggestions on how to fix this?
> >
> > thanks!
> > Evan
> >
> >
> >
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the
> > unregister command
> >     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
>
> __________________________________________________
> Do You Yahoo!?
> Make international calls for as low as $.04/minute with Yahoo! Messenger
> http://phonecard.yahoo.com/
>