postgresql - GROUP BY in UPDATE FROM clause

Question

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?

Answer 1

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;

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;

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?