All Forums Database
reddy0910 1 post Joined 04/16
01 Apr 2016
Update current row from previous row value until hits a condition

Hi,
I have a scenarion where I need to update the next rows untill it hits a condition and replicate the same.
Sample data:-
ID    COL1
1    abc
2     0
3     0
4     0
5     pqr
6     0
7     0
8     0
9    abc
10   0
11   0
12   0
13   0
14  xyz
15   0
16   0
17   0
18   0
 
And my result after update should replace the 0 with value from  previous value as below
ID    COL1
1    abc
2     abc
3     abc
4     abc
5     pqr
6     pqr
7     pqr
8     pqr
9    abc
10   abc
11   abc
12   abc
13   abc
14  xyz
15   xyz
16   xyz
17   xyz
18   xyz

How do I write the update statement?

dnoeth 4628 posts Joined 11/04
01 Apr 2016

Are you on TD14.10?
Then use this Select as source for your Update:

select ID
  ,last_value(NULLIF(COL1,'0') ignore nulls)
   over(order by ID) as new_val
from tab
qualify COL1 = '0' -- return only the changed rows

 

Dieter

You must sign in to leave a comment.