Update rows based on values present in forthcoming rows

oracle, oracle10g, oracle11g, sql

Solution

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   

Problem

Original source