CODEWITHSUNDEEP
sqlsql-server
Ironman10
Ironman10

Reputation: 259

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

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?

enter image description here

Upvotes: 4

Views: 881

Answers (4)

Shushil Bohara
Shushil Bohara

Reputation: 5656

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

Upvotes: 4

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

Try this

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

Upvotes: 3

TT.
TT.

Reputation: 16137

This is a quick script to show the concept. For each row, find the next RN where the value is non-zero, 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;

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

Assuming RN is the column by which we determine column orders:

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 join the table to itself based on finding a later row with a non-zero value. We then perform a further join that tries to locate a "better" match for the later row. Only if that join fails (the final null check in the where clause) do we know that we've located the best match in t2.

Upvotes: 2

Related Questions