PostgreSQL: UPDATE using aggregate function

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); 

I don't know how efficient this is, but it should work:

with updates as ( select p.gid, sum (i.dist) as dist from paneldata p join ibaselang i on p.id = i.gid and i.instp < p.period where i.dist != 0 group by p.gid ) update paneldata p set ibase = 1 / u.dist from updates u where p.gid = u.pid and u.dist != 0 

A couple of other notes about additions:

1. If your i.dist has a lot of zeroes, removing the zero values in the where clause could theoretically speed up your query
2. You have a potential vulnerability for a divide by zero error -- that is also handled in the update statement. Maybe your data doesn't have this condition, but it's best to handle it to be sure

Try using a correlated query like this:

UPDATE paneldata p SET p.ibase=(select SUM(1/i.dist) FROM ibaselang i WHERE p.gid=i.gid AND i.instp<p.period)