PostgreSQL - Update table with previous values ​from another table

Question

I want to update a table with the sum of a second table

This is the table 'x' that I want to update. Has a starting value and a closing value:

id	op_date	initial_value	end_value
1	2020-02-01	0	0
1	2020-02-02	0	0
2	2020-02-01	0	0
2	2020-02-02	0	0

The table 'y' save the values ​​of the day:

id	op_date	value_day
1	2020-01-29	500
1	2020-02-01	100
1	2020-02-02	200
2	2020-01-29	750
2	2020-02-01	100
2	2020-02-02	250

I want the result to look like this:

id	op_date	initial_value	end_value
1	2020-02-01	500	600
1	2020-02-02	600	800
2	2020-02-01	750	850
2	2020-02-02	850	1100

I tried this script, but the process just runs it and doesn't finish it:

UPDATE x
SET 
    initial_value= (select sum(y.value_day) 
        from public.y where 
        y.op_date > '2020-11-01' and y.op_date < x.op_date 
        and y.id = x.id),
    end_value= (select sum(y.value_day) 
        from public.y where 
        y.op_date between '2020-11-01' and x.op_date 
        and y.id = x.id);

Answer 1

You can use window function. To understand window function more you can look this link. At first i am writing query to select the value.

select id,op_date,
sum(value_day) over (
    partition by y.id   
    rows between unbounded preceding and current row
  )-value_day as initial_value,
sum(value_day) over (
    partition by y.id   
    rows between unbounded preceding and current row
  ) as end_value
from y

;

This is your update query.

UPDATE x
set initial_value=s_statement.initial_value,
end_value=s_statement.end_value
from 
(select id,op_date,
sum(value_day) over (
    partition by y.id    
    rows between unbounded preceding and current row
  )-value_day as initial_value,
sum(value_day) over (
    partition by y.id   
    rows between unbounded preceding and current row
  ) as end_value
from y) s_statement
where x.id=s_statement.id 
and x.op_date=s_statement.op_date

;

Let me know if its ok with you.