sql - 如何使此SQL更新语句更有效？

我试图将count、sum和average值从一个表添加到另一个表中，但最终为每个值查询相同的数据。我在用PostgreSQL。我将把这个交给专家来学习如何使这个更新声明更有效。这里是：

update "table1" set
"col1" = (SELECT COUNT(*) FROM "table2" WHERE "table2Id" = "table1"."table1Id"),
"col2" = (SELECT AVG("someCol") FROM "table2" WHERE "table2Id" = "table1"."table1Id"),
"col3" = (SELECT SUM("someCol") FROM "table2" WHERE "table2Id" = "table1"."table1Id");

我应该能够运行一次这样的子查询并访问更新的返回值，对吗？

SELECT COUNT(*), AVG("someCol"), SUM("someCol") FROM "table2" WHERE "table2Id" = "table1"."table1Id";

任何帮助都非常感谢。

最佳答案

尝试子查询：

UPDATE table1
SET col1 = YourCount, col2 = YourAverage, col3 = YourSum
FROM table1 t1
INNER JOIN (
    SELECT table2Id, COUNT(*) AS YourCount, AVG(someCol1) YourAverage,
        SUM(someCol2) YourSum
    FROM table2
    GROUP BY table2Id
) t2 ON t1.table1Id = t2.table2Id