How to update a table using a select group by in a second one and itself as the data source in MySQL
Posted
by Jader Dias
on Stack Overflow
See other posts from Stack Overflow
or by Jader Dias
Published on 2010-05-04T13:40:40Z
Indexed on
2010/05/04
14:08 UTC
Read the original article
Hit count: 201
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.
© Stack Overflow or respective owner
