postgresql UPDATE FROM 子句中的 GROUP BY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5272412/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
GROUP BY in UPDATE FROM clause
提问by sennin
I really need do something like that:
我真的需要做这样的事情：
UPDATE table t1
SET column1=t2.column1
FROM table t2
INNER JOIN table t3
USING (column2)
GROUP BY t1.column2;
But postgres is saying that I have syntax error about GROUP BY clause. What is a different way to do this?
但是 postgres 说我有关于 GROUP BY 子句的语法错误。有什么不同的方法可以做到这一点？
回答by Anomie
The UPDATE statement does not support GROUP BY, see the documentation. If you're trying to update t1 with the corresponding row from t2, you'd want to use the WHERE clause something like this:
UPDATE 语句不支持 GROUP BY，请参阅文档。如果您尝试使用 t2 中的相应行更新 t1，您需要使用 WHERE 子句，如下所示：
UPDATE table t1 SET column1=t2.column1
FROM table t2
JOIN table t3 USING (column2)
WHERE t1.column2=t2.column2;
If you need to group the rows from t2/t3 before assigning to t1, you'd need to use a subquery something like this:
如果您需要在分配给 t1 之前对 t2/t3 中的行进行分组，则需要使用如下子查询：
UPDATE table t1 SET column1=sq.column1
FROM (
SELECT t2.column1, column2
FROM table t2
JOIN table t3 USING (column2)
GROUP BY column2
) AS sq
WHERE t1.column2=sq.column2;
Although as formulated that won't work because t2.column1 isn't included in the GROUP BY statement (it would have to be an aggregate function rather than a simple column reference).
尽管由于 t2.column1 不包含在 GROUP BY 语句中（它必须是一个聚合函数而不是一个简单的列引用），所以它不会起作用。
Otherwise, what exactly are you trying to do here?
否则，你到底想在这里做什么？
