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;
Michael
Laszlo Thoth wrote:
I'm trying to create a single UPDATE query to deal with the following problem:
==================================================================
-- I've got two tables:
CREATE TABLE `banannas` (
`owner` varchar(15) NOT NULL default ''
);
CREATE TABLE `monkeys` (
`name` varchar(15) default NULL,
`banannacount` int(4) default NULL
);
-- I've got three monkeys:
INSERT INTO `monkeys` VALUES ('bonzo',NULL),('dunston',NULL),('ham',NULL);
-- Some of those monkeys have banannas.
-- Some of those monkeys have more than one bananna.
-- Some of those monkeys don't have any banannas.
INSERT INTO `banannas` VALUES ('bonzo'),('bonzo'),('bonzo'),('ham');
==================================================================
I'm trying to write an UPDATE query so that monkeys.banannacount is set to the
number of banannas each monkey owns.
mysql> SELECT name,COUNT(banannas.owner) as bc FROM monkeys LEFT JOIN banannas
ON monkeys.name=banannas.owner GROUP BY owner;
+---------+----+
| name | bc |
+---------+----+
| dunston | 0 |
| bonzo | 3 |
| ham | 1 |
+---------+----+
I know it's possible to assign an UPDATE with the product of a join, but the
GROUP BY clause is throwing me off.
mysql> UPDATE monkeys LEFT JOIN banannas ON monkeys.name=banannas.owner GROUP BY
banannas.owner SET monkeys.banannas=COUNT(banannas.owner);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'GROUP BY banannas.owner SET monkeys.banannas=COUNT(banannas.owner)' at line 1
mysql> UPDATE monkeys LEFT JOIN banannas ON monkeys.name=banannas.owner SET
monkeys.banannas=COUNT(banannas.owner) GROUP BY banannas.owner;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'GROUP BY banannas.owner' at line 1
Is this possible without subqueries? Is this possible at all?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
