Thread: Problems with UPDATE
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
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: > 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/ >
Forgot to mention: these profiles are about 6000 rows long. 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/ >
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.