Getting Values of the two rows before the selected row POSTRESQL
You can use either a window function:

with ct as
(
  select
      id,
      amount,
      (sum(amount) over (order by id rows between 2 preceding and 1 preceding)) / 2 as new_amount
  from
      t
)
update t
set    amount = ct.new_amount
from
    ct
where
    t.id = ct.id
    and ct.id = 3;


Or use a subquery that returns the 2 previous rows:

update t
set amount = (select sum(t3.amount) / 2
             from (select amount 
                   from t t2
                   where t2.id &lt; t.id 
                   order by id desc 
                   limit 2) t3)
where
    t.id = 3;


Check them for performance.




select * from t;



id |             amount
-: | -----------------:
 1 |                  1
 2 |                  3
 3 | 2.0000000000000000



db&lt;>fiddle here