Problems with UPDATE

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/

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:

Forgot to mention: these profiles are about 6000 rows long.

On Mon, 20 Aug 2001, Andrew Gould wrote:

On Mon, 20 Aug 2001 22:08:22 +0000 (UTC), <macosko@fas.harvard.edu>:

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.

So, you have a list of
orf, induction

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

Not sure what is temp... is this the list from above?

table, yeast1, is very big--perhaps 600 columns. Basically, I'm just

Not sure what the number of columns has to do with it. Do you have
each orf in its own column for some reason?

UPDATE is used to modify a previously existing row. Is that what
you are wanting to do? Or are you wanting to insert new rows?

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.

This is what makes me think you are adding columns for new
orfs. Seems like a bad idea to me if that is what you are doing.

(Of course the whole thing has me quite confused, soo...)

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!

You might try posting your schema... though then again, with
600 columns maybe that is not such a good idea.