Mysql multi-table update with group by

toma42

Hey now, I can't find any info on this:

Using mysql 4, I'd like to do a multi-table update using a group by function in the set clause. Here's the sql (which works find if group by and avg() are removed):

UPDATE somelist, userrating
SET somelist.rating = avg(userrating.rating)
WHERE somelist.user_key = userrating.ref_user
GROUP BY userrating.ref_user;

Can anyone confirm that what I'm trying to do is impossible with mysql?

Thanks,
Tom

stolzyboy

got this from here

Starting with MySQL Version 4.0.4, you can also perform UPDATE operations that cover multiple tables:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;
Note: you can not use ORDER BY or LIMIT with multi-table UPDATE.

toma42

Yup, I read that too, but it says nothing about group by. Of course, to do a group by the server needs to do an order by internally but only on one of the tables...

stolzyboy

Originally posted by toma42
Yup, I read that too, but it says nothing about group by. Of course, to do a group by the server needs to do an order by internally but only on one of the tables...

, which could be the underlying problem.