How to update a table using a select group by in a second one and itself as the data source in MySQL?

Question

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.

Answer 1

You are using MySQL's extension to GROUP BY which should not be used in this context.

Given the following values:

tableA

value somekey
1     1
2     1

tableB

value somekey
3     1
4     1

this subquery:

    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

will return you either 8 or 9: it will calculate SUM(b.value) = 7 and then add a random value from A for the given key.

For this sample data, which value do you want the speed to be updated to?

Answer 2

You can do it by rewriting your query:

UPDATE tableA t1, (
   SELECT somekey, SUM(value) value
   FROM tableB t3
   GROUP BY somekey
) t2
SET speed = t1.value + t2.value
WHERE t1.somekey = t2.somekey;