How to update zeros with the first non-zero value from a following row?

Question

How do I UPDATE the zeros with the next non-zero values in col2?

For eg - I need the first 3 rows in col2 to be updated with the value 2. Similarly, I need the next next two values after 2 to be updated with 3 (for RN 5,6)

Basically, after every trailing zeros, if there is a non-zero, it should be updated in the above trailing zeros. How can I do this using loops and procedures?

Answer 1

Here is the simple query for this, you can just achieve this by using OUTER APPLY

CREATE TABLE #tmp(rn INT IDENTITY(1,1), col1 VARCHAR(50), col2 INT, col3 DATE)
INSERT INTO #tmp VALUES 
('ABC', 0, '2017-01-01'),
('ABC', 0, '2017-01-01'),
('ABC', 0, '2017-01-01'),
('ABC', 2, '2017-01-01'),
('ABC', 0, '2014-02-01'),
('ABC', 0, '2013-02-01'),
('ABC', 3, '2013-01-01'),
('ABC', 0, '2017-01-01'),
('ABC', 0, '2017-01-01'),
('ABC', 0, '2017-01-01'),
('ABC', 0, '2017-01-01'),
('ABC', 7, '2017-01-01')


UPDATE t SET t.col2 = t2.col2
FROM #tmp t
OUTER APPLY(SELECT TOP 1 col2 FROM #tmp WHERE rn >= t.rn AND col2 > 0) t2

OUTPUT:

rn  col1    col2    col3
1   ABC     2       2017-01-01
2   ABC     2       2017-01-01
3   ABC     2       2017-01-01
4   ABC     2       2017-01-01
5   ABC     3       2014-02-01
6   ABC     3       2013-02-01
7   ABC     3       2013-01-01
8   ABC     7       2017-01-01
9   ABC     7       2017-01-01
10  ABC     7       2017-01-01
11  ABC     7       2017-01-01
12  ABC     7       2017-01-01