multi table join, suggestions?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

multi table join, suggestions?

Post 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
User avatar
Lord Sauron
Forum Commoner
Posts: 85
Joined: Tue Apr 20, 2004 5:53 am
Location: Tilburg, NL

Post 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.
Post Reply