UPDATE column with previous value

Question

Imagine a sample table

CREATE TABLE mytable (myid integer, myval double precision);

I want to update myval to the previous non-zero value, ordered by myid, if myval is equal to 0.

For that to work properly, the query has to start updating from the lowest myid and end with the highest one.

I really don't know from where to start this time. The following says that window functions are not allowed in UPDATE:

UPDATE mytable
SET myval = LAG(myval) OVER (ORDER BY myid)
WHERE myval = 0
RETURNING *;

And more complex alternatives with FROM subqueries have ended in syntax errors or dumb outputs because the subquery is evaluated once instead of once per row. This last phrase makes me think about the SELECT ... LEFT JOIN LATERAL ... structure, but I haven't been able to make it work with the update statement.

Answer 1

update mytable t set myval=(select s.myval from mytable s where s.myid < t.myid and s.myval!=0 order by s.myid desc limit 1) where t.myid in (select myid from mytable where myval=0 order by myid for update) ;

results

select * from mytable;
 myid | myval
------+-------
    1 |     1
    2 | 0.123
    3 |     0
    4 |     5
    7 |     0

update mytable t set myval=(select s.myval from mytable s where s.myid < t.myid and s.myval!=0 order by s.myid desc limit 1) where t.myid in (select myid from mytable where myval=0 order by myid for update) ;

select * from mytable order by myid;
 myid | myval
------+-------
    1 |     1
    2 | 0.123
    3 | 0.123
    4 |     5
    7 |     5