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]
