Query Problem, JOINs and COUNT()s :-(

[l008com]

UPDATE rest,item SET rest.total=COUNT(item.rid) WHERE rest.id = item.rid

So theres my short query. Per the mysql docs, mysql 4.0.4 and later can do joins in UPDATEs. Im on mysql 4.1.something so I'm good in that department. Problem is, when I run this, I get an error complaining about invalid use of group functions (#1111). I don't understand why I need a group by, or how to figure out which I should use, but just for the hell of it, I tried GROUP BY rest.total and item.rid and every single other referenced table and table.field, and still nothing. When I try to add a group by, it then complains about improper use of group by, or something like that. I don't see what I'm doing wrong here, seems like this little query should just work.

[kakki]

maybe the "count" is wrong???
( i've never seen this type of query, but i encountered the error, when i was using count
and another column without the group-by clause

[rebelo]

Don't you need to link the tables based on a criteria, though? When I do a join in MySQL, I use something like

SELECT t1.foo, t2.bar FROM t1 LEFT JOIN t2 ON (t1.id=t2.id);

Even if you aren't doing a left/right join, I would assume you need to specify the common key with an ON statement or similar?

Alternately, you might try bracketting the where clause in your original statement...

UPDATE rest,item SET rest.total=COUNT(item.rid) WHERE (rest.id = item.rid);