如何使用第二个选择组及其本身作为MySQL中的数据源来更新表? [英] How to update a table using a select group by in a second one and itself as the data source in MySQL?

问题描述

我可以这样做:

SELECT t2.value + sum(t3.value)
FROM tableA t2, tableB t3
WHERE t2.somekey = t3.somekey
GROUP BY t3.somekey

但是该怎么做?

 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认为这是非法的，因为您无法在FROM子句中指定目标表t1进行更新.

MySQL says it's illegal since you can't specify target table t1 for update in FROM clause.

推荐答案

您可以通过重写查询来实现:

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;

这篇关于如何使用第二个选择组及其本身作为MySQL中的数据源来更新表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！