Sorry, I meant to send this reply to the whole mailing list, not just to Shawn.
Rhino ----- Original Message ----- From: "Rhino" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, September 14, 2004 2:18 PM Subject: Re: update/join question.. > Okay, I stand corrected. Apparently, MySQL allows updates of joins. > > My apologies, I didn't mean to steer anyone down the wrong path. > > Thanks for setting me straight, Shawn! > > Rhino > > ----- Original Message ----- > From: <[EMAIL PROTECTED]> > To: "Rhino" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Tuesday, September 14, 2004 2:07 PM > Subject: Re: update/join question.. > > > > 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] > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
