Update rows based on values present in forthcoming rows

Question

I have a table with below data

-------------------
ID    Key   Value
-------------------
1      1           
2      0
3      1      
4      0
5      0
6      0
7      1
8      0
9      0
--------------------

I want to update the Value column as below

-------------------
ID    Key   Value
-------------------
1      1      0     
2      0      1
3      1      0      
4      0      3
5      0      2
6      0      1
7      1      0
8      0      0
9      0      0
--------------------

That is, every Key=1 will have Value= 0. Every Key=0 will have the Value = Number of traverses from current row to row which has Key= 1. And the last two Key, since there is no '1' to follow, will have the Value=0.

I need a plain Oracle SQL Update statement for this.

Answer 1

SQL> create table t (id int, key int, value int);

SQL> insert into t (id, key)
  2  select * from
  3  (
  4  select 1 x,  1 y from dual union all
  5  select 2,  0 from dual union all
  6  select 3,  1 from dual union all
  7  select 4,  0 from dual union all
  8  select 5,  0 from dual union all
  9  select 6,  0 from dual union all
 10  select 7,  1 from dual union all
 11  select 8,  0 from dual union all
 12  select 9,  0 from dual
 13  )
 14  /

Создано строк: 9.

SQL> commit;

SQL> select * from t;

  ID        KEY      VALUE                                                      
---- ---------- ----------                                                      
   1          1                                                                 
   2          0                                                                 
   3          1                                                                 
   4          0                                                                 
   5          0                                                                 
   6          0                                                                 
   7          1                                                                 
   8          0                                                                 
   9          0                                                                 

SQL> merge into t using(
  2  select id, key,
  3  decode(key,1,0,
  4  decode((max(key) over(order by id rows between current row and unbounded following)),0,0,
  5  sum(decode(key,0,1)) over(partition by grp order by id rows between current row and unbounded following))
  6  )
  7  value
  8  from (
  9  select id, key, decode(key,1,0,
 10         decode((max(key) over(order by id rows between current row and unbounded following)),0,0,  -- Define if there is 1 below
 11         (sum(key) over(order by id rows between current row and unbounded following))
 12         )) grp
 13  from t
 14  )
 15  ) src
 16  on (t.id = src.id)
 17  when matched then
 18    update set t.value = src.value
 19  /

SQL> select * from t;

  ID        KEY      VALUE                                                      
---- ---------- ----------                                                      
   1          1          0                                                      
   2          0          1                                                      
   3          1          0                                                      
   4          0          3                                                      
   5          0          2                                                      
   6          0          1                                                      
   7          1          0                                                      
   8          0          0                                                      
   9          0          0