multi table join, suggestions?
Posted: Thu Jul 22, 2004 9:33 am
From the mySQL documentation site:
"Note: You cannot use ORDER BY or LIMIT with multiple-table UPDATE."
that begs the question: "WHY NOT??!?!"
so i made a select statement that gets me all the good info i need, then i want to update that info... here is my update query, that of course does not work... this query will be updating lots of rows, and was wanting to avoid a nasty loop in php to do this, as that would be hundreds of queries, mabe thousands.
basicly what the query does is this: looks at the events table, gets the person tied to that event, uses his location, joins the location of the place he serves, and then picks him the next person in the location he serves, and sets their served field to 1. this should only happen for one person for each row in the event table, which is why i tried the LIMIT clause.
here is my unworking query, any suggestions on one that would work?
"Note: You cannot use ORDER BY or LIMIT with multiple-table UPDATE."
that begs the question: "WHY NOT??!?!"
so i made a select statement that gets me all the good info i need, then i want to update that info... here is my update query, that of course does not work... this query will be updating lots of rows, and was wanting to avoid a nasty loop in php to do this, as that would be hundreds of queries, mabe thousands.
basicly what the query does is this: looks at the events table, gets the person tied to that event, uses his location, joins the location of the place he serves, and then picks him the next person in the location he serves, and sets their served field to 1. this should only happen for one person for each row in the event table, which is why i tried the LIMIT clause.
here is my unworking query, any suggestions on one that would work?
Code: Select all
UPDATE event e
INNER JOIN people p ON e.person_id = p.person_id
INNER JOIN location l ON p.location_id = l.location_id
INNER JOIN location l2 ON p.location_2 = l2.location_id
LEFT JOIN people p2 ON p2.location_id = p.location_2
SET p2.served = 1
WHERE e.time_left = 0 ORDER BY p2.order DESC LIMIT 1