Rhino, I hate to burst your bubble but JOINS __are allowed__ in MySQL updates. I use them all the time.
http://dev.mysql.com/doc/mysql/en/UPDATE.html At the top of the page are the syntaxes for single-table and multiple-table updates: The key here is that whatever you put _between_ the words FROM and WHERE in a normal query will be what you need to UPDATE. (I personally discourage anyone from using the "comma join" method of declaring table joins so I won't use it in my examples. It's a valid syntax but if you forget to put the right comparisons into your WHERE clause, you end up with problems. It's even easier with using JOINS in an UPDATE as the WHERE clause is even farther away from the tables) This query will give me a list of all of the companies who have contracts managed by manager 15: SELECT DISTINCT t1.* FROM Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_id WHERE t2.Manager_ID = 15 Imagine a situation where you needed to update contract.ManagerID with a new manager (Manager 15 was promoted) but you are not reassigning ALL of the contract accounts to the same person. You want to split it up so that companies A-L go to manger 12 and companies M-Z to manager 34. You *CAN* do this in a single statement but that would confuse my example. I will do the reassignments in two statements so that you can see the pattern better: UPDATE Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_ID SET t2.Manager_Id = 12 WHERE t2.Manager_ID = 15 AND t1.Name <'m'; UPDATE Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_ID SET t2.Manager_Id = 34 WHERE t2.Manager_ID = 15 AND t1.Name >= 'm'; The WHERE clause didn't need to change but what came after the FROM in a query has been moved to right after the UPDATE. The SET clause can assign values to or get values from ANY column from any of the tables participating in the UPDATE clause. Let me get back on topic... Bruce, you started with this SELECT: 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 INNER JOIN latestParseStatusTBL as l1 on p1.fileID = l1.itemID WHERE u1.universityID='40'; You should be able to transform this query into: UPDATE university_urlTBL as u1 RIGHT JOIN parsefileTBL as p1 on u1.ID =p1.university_urlID INNER JOIN latestParseStatusTBL as l1 on p1.fileID = l1.itemID SET .... WHERE ... Your SET and WHERE clauses can refer to any column in any of the three tables participating in the JOINs. Guessing from your example they would look like SET l1.process = '1', l1.status = '13' WHERE u1.universityID = '40' <BEGIN RANT> If "process", "status", and "universityID" are numeric fields... DROP THOSE QUOTES. They are not necessary and require the engine to take an extra conversion. <END RANT> sorry... just had to get that off my chest. :-D Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Rhino" <[EMAIL PROTECTED]> wrote on 09/14/2004 01:24:19 PM: > > ----- 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] >
