PostgreSQL: UPDATE using aggregate function

aggregate-functions, postgresql, sql

Solution

Here's a generic example of how to do it.

UPDATE public.customer_value cv
SET total_value = sub_q.sum_val 
FROM 
    (
    SELECT SUM(order_amount) AS sum_val, o.customer_id 
    FROM public.orders AS o
    GROUP BY o.customer_id
    ) AS sub_q
WHERE sub_q.customer_id = cv.customer_id;

If you want to try this example out in full you can create the dummy data like this:

CREATE TABLE public.customer_value
(
  customer_id int 
, total_value numeric (10,2)
);

CREATE TABLE public.orders 
(
  customer_id int
, order_amount numeric(10,2)
);

INSERT INTO public.customer_value
(customer_id)
VALUES 
  (1)
, (2);


INSERT INTO public.orders
(customer_id, order_amount)
VALUES 
 (1, 10)
,(1, 10)
,(2, 7.5)
,(2, 7.5);

Problem

I want to update the table `paneldata` setting the column `ibase` using an aggregate function. ``` UPDATE paneldata p SET ibase=SUM(1/i.dist) FROM ibaselang i WHERE p.gid=i.gid AND i.instp<p.period ``` This results in `ERROR: aggregate functions are not allowed in UPDATE` TABLE DEFINITIONS ``` CREATE TABLE public.ibaselang ( gid integer, dist double precision, buildid integer, instp smallint ) WITH ( OIDS=FALSE ); ``` Solution Approach Unfortunately I don't know how to implement my `WHERE` functions in a subquery.

Original source