List:General Discussion« Previous MessageNext Message »
From:SGreen Date:September 14 2004 6:07pm
Subject:Re: update/join question..
View as plain text  
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
> 

Thread
update/join question..bruce14 Sep
  • Re: update/join question..Rhino14 Sep
    • Re: update/join question..SGreen14 Sep
  • Re: update/join question..SGreen14 Sep
  • Re: update/join question..Oliver Schiessl15 Sep