Update Row with Previous Row

Question

I have the following table:

I am trying to write an update that fills the zeros with previous value greater than zero

I have tried with the following querys without success

1.

With cte As
(
    SELECT [ID], [Q_G_R_BUOM], ROW_NUMBER() OVER (ORDER BY [ID] Asc) AS RN
    FROM [Testing_Lag]
)
update cte set [Q_G_R_BUOM]=iif([Q_G_R_BUOM]>0,[Q_G_R_BUOM],(SELECT [Q_G_R_BUOM] FROM cte WHERE [ID]=[ID]-1))

With cte As
(
    SELECT [ID], [Q_G_R_BUOM], ROW_NUMBER() OVER (ORDER BY [ID] Asc) AS RN
    FROM [Testing_Lag]
)
update cte set [Q_G_R_BUOM]=iif([Q_G_R_BUOM]>0,[Q_G_R_BUOM],(Lag([Q_G_R_BUOM], 1) OVER(ORDER BY [ID] ASC)))

I would be really grateful if someone could help me out Thanks

user_1144035 · Answer

One method in SQL Server uses an updatable CTE and window functions:

with toupdate as (
      select tl.*,
             max(Q_G_R_BUOM) over (partition by grp) as imputed_Q_G_R_BUOM
      from (select tl.*,
                   sum(case when Q_G_R_BUOM <> 0 then 1 else 0 end) over (order by id) as grp
            from testing_lag tl
           ) tl
     ) 
update toupdate
    set Q_G_R_BUOM = imputed_Q_G_R_BUOM
    where Q_G_R_BUOM = 0;

This assigns a grouping to each row based on the number number of non-zero values up to that row. It then "spreads" that value over the entire group.

An alternative method uses apply:

update tl
    set q_g_r_buom = tl2.q_g_r_buom
    from test_lag tl cross apply
         (select top (1) tl2.*
          from test_lag tl2
          where tl2.q_g_r_buom <> 0 and
                tl2.id < tl.id
          order by tl2.id
         ) tl2
    where tl.q_g_r_buom = 0

user_5212614 · Answer

How about this?

--DROP TABLE #C
CREATE TABLE #C (ID INT, Q_G_R_BUOM INT)
INSERT INTO #C values(1, 1805)
INSERT INTO #C values(2, 0)
INSERT INTO #C values(3, 0)
INSERT INTO #C values(4, 4732)
INSERT INTO #C values(5, 0)
INSERT INTO #C values(6, 0)

select * from #c

If you have nulls in your table, the solution would be a bit different.

SELECT ID,CASE WHEN Q_G_R_BUOM >0
            THEN Q_G_R_BUOM
            ELSE (SELECT max(Q_G_R_BUOM)
                  FROM #C
                  WHERE ID <= t.ID)
       END AS X
FROM #C t

DROP TABLE #C
CREATE TABLE #C (ID INT, Q_G_R_BUOM INT)
INSERT INTO #C values(1, 1805)
INSERT INTO #C values(2, null)
INSERT INTO #C values(3, null)
INSERT INTO #C values(4, 4732)
INSERT INTO #C values(5, null)
INSERT INTO #C values(6, null)

select * from #c

SELECT ID,
COALESCE(Q_G_R_BUOM,
MAX(COALESCE(Q_G_R_BUOM,'')) OVER (ORDER BY ID  ROWS BETWEEN UNBOUNDED PRECEDING AND  1 PRECEDING)) AS Name
FROM #c

Update Row with Previous Row

2 Answers