Getting Values of the two rows before the selected row POSTRESQL I want to update an row according to the values of two rows before the row how can i do that?

Let's assume my table looks like this

ID         amount

1           1
2           3
7           5


And i want to do this

UPDATE amount 
FROM table 
SET amount = (AmounToFRowOneBefore + AmounToFRowTwoBefore)/2  
WHERE ID=7;


Expected output would be

 ID         amount

    1           1
    2           3
    7           2


And if I do this I don't know anything about the two rows before so no ID or anything is there a query to select the values before the selected row?
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