Page 1 of 1

multi table join, suggestions?

Posted: Thu Jul 22, 2004 9:33 am
by liljester
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?

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

Posted: Mon Aug 30, 2004 5:44 am
by Lord Sauron
You cannot use ORDER BY, because this function is only for sorting data that has to be represented on screen. UPDATE only stores data in the database, but doesn't represent data. Sorting the data is therefore unnecessary.

I'm not sure of LIMIT, because I never use this one, but I guess this is because of the same reason.