How to update a table using a select group by in a second one and itself as the data source in MySQL
- by Jader Dias
I can do this:
SELECT t2.value + sum(t3.value)
FROM tableA t2, tableB t3
WHERE t2.somekey = t3.somekey
GROUP BY t3.somekey
But how to do this?
UPDATE tableA t1
SET speed = (
SELECT t2.value + sum(t3.value)
FROM tableA t2, tableB t3
WHERE t2.somekey = t3.somekey
AND t1.somekey = t3.somekey
GROUP BY t3.somekey
)
;
MySQL says it's illegal since you can't specify target table t1 for update in FROM clause.
