Quoting Michael Stassen <[EMAIL PROTECTED]>: > According to the manual <http://dev.mysql.com/doc/mysql/en/UPDATE.html>, the > multi-table UPDATE syntax is > > UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...] > SET col_name1=expr1 [, col_name2=expr2 ...] > [WHERE where_definition] > > so, you can join tables, but you can't GROUP BY. > > You could do this with a temporary table to hold the counts: > > CREATE TEMPORARY TABLE banana_count SELECT name, COUNT(banannas.owner) bc > FROM monkeys LEFT JOIN banannas > ON monkeys.name=banannas.owner > GROUP BY owner; > > UPDATE monkeys JOIN banana_count USING (name) > SET banannacount=bc; > > DROP TABLE banana_count;
What if I want to just update one monkey's bananna count without temporary tables? I think I can run this query without a GROUP, but mysql doesn't like the query. mysql> SELECT COUNT(banannas.owner) as bc FROM banannas WHERE owner="dunston"; +----+ | bc | +----+ | 0 | +----+ 1 row in set (0.07 sec) mysql> UPDATE monkeys LEFT JOIN banannas ON monkeys.name=banannas.owner SET monkeys.banannas=COUNT(banannas.owner) WHERE monkeys.name="ham"; ERROR 1111 (HY000): Invalid use of group function mysql> UPDATE monkeys LEFT JOIN banannas ON monkeys.name=banannas.owner AND monkeys.name="ham" SET monkeys.banannas=COUNT(banannas.owner); ERROR 1111 (HY000): Invalid use of group function -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
