pray, that you use mysql version >=4
in version 3 it doesnt work...
Oliver
On Tue, 14 Sep 2004 14:09:43 -0400, <SGreen@stripped> wrote:
> 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" <rhino1@stripped> wrote on 09/14/2004 01:24:19 PM:
>
>>
>> ----- Original Message -----
>> From: "bruce" <bedouglas@stripped>
>> To: <mysql@stripped>
>> 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/mysql?unsub=1
>>
--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/
