----- Original Message ----- From: "bruce" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, September 14, 2004 11:45 AM Subject: update/join question..
> hi... > > a question on how to do an update on a table that depends on 'left/right' > joins with other tables... > > i have the following select that works. > > select > u1.urltype as type, > p1.fileID as fileID, > l1.process as process, > l1.status as status > from university_urlTBL as u1 > right join parsefileTBL as p1 > on u1.ID =p1.university_urlID > join latestParseStatusTBL as l1 > on p1.fileID = l1.itemID > where u1.universityID='40'; > > > i simply wnat to be able to update the latestParseStatusTBL based upon the > joins between the tables... > > (something like...) > update > latestParseStatusTBL, > university_urlTBL as u1 > right join parsefileTBL as p1 > on u1.ID =p1.university_urlID > join latestParseStatusTBL as l1 > on p1.fileID = l1.itemID > where u1.universityID='40' > set > l1.process = '1', > l1.status = '13'; > > i've tried a number of derivatives of this approach with no luck.. i'm > missing something simple.. > You haven't explained what you meant by "with no luck"; did the updates fail with an error message or without a message? If there was a message, what did it say? I'm guessing that they failed with an error message but that the message was cryptic.... I'm not overly fluent with MySQL yet but in 20 years of using DB2, I've always been told that you can't update a join, you can only update an individual table. I assume that is true of all relational databases, including MySQL, but I don't know that for an absolute fact. > searching through mysql.com/google hasn't shed much light!! > Perhaps because this is such a fundamental concept that no one thought it worth putting in a manual; they just assumed it would be told to you wherever you learned basic relational concepts. If so, that is assuming that everyone working with relational databases had some exposure to theory first; I think that's a very dubious assumption. > any ideas/comments/asssistance/thoughts/etc.. > There ought to be a clear error message every time you try to update, insert, or delete from a join that says these operations cannot be done on joins. The manuals for every relational database should state that Insert, Update, and Delete do not work on joins, both in the reference section for the Insert, Update, and Delete statements and in the concepts section. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
