Dynamic update in SQL based on previous value

Question

I am trying to explore the dynamic update.

My actual source table is:

Expected result of the source table after update :

The query i tried :

WITH t AS
(
    SELECT key,
           Begin_POS,
           Length,
           (Begin_POS+ Length) as res
    from   tab
)
SELECT src_column_id,
       Length,res,
       COALESCE(Length + lag(res) OVER (ORDER BY src_column_id),1)  AS PRE_VS
from t

Can you assist what should be my approach like ?

Answer 1

I think that’s a window sum:

select 
    t.*,
    1 + coalesce(
        sum(length) over(
            order by key 
            rows between unbounded preceding and 1 preceding
        ), 
        0
    ) new_begin_pos
from mytable t