Search code examples
mysqlmysql-error-1093

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

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.

Solution

  • 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;