How to update zeros with the first nonzero value from the next line?

Question

How to update zeros with the following nonzero values ​​in col2?

For example, I need the first 3 lines in col2 to be updated with a value of 2. Similarly, I need the following two values ​​after 2 that need to be updated with 3 (for RN 5,6)

Basically, after each trailing zero, if there is a non-zero value, it should be updated at the zero zeros given above. How to do this using loopsand procedures?

Answer 1

Here is a simple request for this, you can simply achieve this 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

Answer 2

try it

DECLARE @T TABLE
(
    RN INT IDENTITY(1,1),
    COL1 VARCHAR(20),
    COL2 INT,
    COL3 DATE,
    COL2_NEW INT NULL
)
INSERT INTO @T(COL1,COL2,COL3)
VALUES('ABC',0,'1/1/2017'),
('ABC',0,'01/01/2017'),
('ABC',0,'01/01/2017'),
('ABC',2,'01/01/2017'),
('ABC',0,'01/01/2017'),
('ABC',0,'01/01/2017'),
('ABC',3,'01/01/2014'),
('ABC',0,'01/01/2017')

;WITH CTE
AS
(
    SELECT
        SeqNo = ROW_NUMBER() OVER(PARTITION BY T1.RN ORDER BY T1.RN,T2.RN),
        T1.RN,
        T2RN = T2.RN,
        T2.COL2
        FROM @T T1
            INNER JOIN @T t2
                ON T1.COL1 = T2.COL1    
                    AND T1.COL2 = 0
                    AND T2.COL2 > 0
                    AND T1.RN < T2.RN
)
UPDATE @T
    SET 
        COL2_NEW = CTE.COL2
    FROM CTE
        INNER JOIN @T T
            ON T.RN = CTE.RN
        WHERE CTE.SeqNo = 1

SELECT
    *
    FROM @T

Answer 3

, RN - , :

UPDATE t
SET col2 = t2.col2
FROM
    table t
        inner join
    table t2
        on
            t.rn < t2.rn and
            t2.col2 != 0
        left join
   table t_anti
       on
           t.rn < t_anti.rn and
           t_anti.rn < t2.rn and
           t_anti.col2 != 0
where
    t.col2 = 0 and
    t_anti.rn is null

Basically, we attach the table to ourselves, based on finding a later row with a non-zero value. Then we make an additional join, which tries to find a "better" match for a later line. Only if this connection fails (final nullcheck in the sentence where), we know that we have found the best match in t2.

Answer 4

This is a quick script to show the concept. For each row, find the next RN where the value is nonzero, then update the value to the value for that RN.

CREATE TABLE #tt(RN INT IDENTITY(1,1) PRIMARY KEY, val INT);
INSERT INTO #tt(val)VALUES(0),(0),(0),(2),(0),(0),(3),(0);
SELECT * FROM #tt;

UPDATE
    #tt
SET
    val=ISNULL((SELECT val FROM #tt AS r WHERE r.RN=mrn.RN),0)
FROM
    #tt AS o
    CROSS APPLY (
        SELECT
            RN=MIN(RN)
        FROM
            #tt AS i
        WHERE
            i.val<>0 AND i.RN>=o.RN
    ) AS mrn;

SELECT * FROM #tt;

DROP TABLE #tt;