Update current row from previous row value until hits a condition

Database

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?

Tags (1)
1 REPLY
Senior Apprentice

Re: Update current row from previous row value until hits a condition

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