GROUP BY在UPDATE FROM子句中 [英] GROUP BY in UPDATE FROM clause
问题描述
我真的需要做这样的事情:
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;
但是postgres说我有关于GROUP BY子句的语法错误。
But postgres is saying that I have syntax error about GROUP BY clause. What is a different way to do this?
推荐答案
UPDATE语句不支持GROUP BY,请参阅文档。如果你想用t2中相应的行更新t1,你可能想使用如下的WHERE子句:
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 table t1 SET column1=t2.column1
FROM table t2
JOIN table t3 USING (column2)
WHERE t1.column2=t2.column2;
如果您需要在分配给t1之前将t2 / t3中的行分组,使用子查询,如下所示:
If you need to group the rows from t2/t3 before assigning to t1, you'd need to use a subquery something like this:
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;
尽管按照公式制定,但由于t2.column1不包含在GROUP BY语句中(它必须是一个集合函数,而不是一个简单的列引用)。
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).
否则,你到底在做什么?
Otherwise, what exactly are you trying to do here?
这篇关于GROUP BY在UPDATE FROM子句中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!