CODEWITHSUNDEEP
sqlloopst-sql
Incompetent77
Incompetent77

Reputation: 81

How can you update a table using the lag function on a loop?

I've already spent hours trying to figure this out but I can't, so I turn to the experts now. This is how my table looks like:

RowNumber    SeriesNumber
1                  1
2                  0
3                  0
4                  0
1                  2
2                  0
1                  3
2                  0
1                  4

This is how I want it to look like:

RowNumber    SeriesNumber
1                  1
2                  1
3                  1
4                  1
1                  2
2                  2
1                  3
2                  3
1                  4

I basically want all the zeros (one by one, so I guess on a loop?) to take the value of the row directly above them. Is it really that difficult or I am just not smart enough?

Thanks!

Upvotes: 2

Views: 1156

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

If you have a rowNumberTotal column as an overall ordering, then this is pretty easy. Here is a mechanism using window functions:

with toupdate as (
      select t.*,
             max(seriesnumber) over (partition by prev_rownumbertotal) as imputed_seriesnumber
      from (select t.*,
                   max(case when seriesnumber > 0 then rownumbertotal end) over (order by rownumbertotal) as prev_rownumbertotal
            from t
           ) t
     )
update toupdate
    set seriesnumber = imputed_seriesnumber
    where seriesnumber = 0;

Upvotes: 1

GMB
GMB

Reputation: 222592

For this to be solvable, you do need a column that can be used to order the records, like an identity column. I assume that such a column exists and is called id.

You can use an updatable CTE:

with cte as (
    select t.*, lag(SeriesNumber) over(order by id) lagSeriesNumber
    from mytable t
)
update cte set SeriesNumber = lagSeriesNumber where SeriesNumber = 0

Upvotes: 1

Related Questions