sql server - SQL Fill in blank data based on previous rows -

i have sql table similar following:

id, date,   d1, d2, d3 1   1/1/13  0   x   2   1/2/13           3   1/3/13  1 4   1/4/13          b 5   1/5/13   

that need update following:

id, date,   d1, d2, d3 1   1/1/13  0   x   2   1/2/13  0   x          3   1/3/13  1   x      4   1/4/13  1   x   b 5   1/5/13  1   x   b 

basically filling in nulls value before it. going performed once. loop best option? or there more effective?

presuming you're using sql-server can use common-table-expression:

with cte       (select t1.id,                  t1.date,                  d1 = coalesce(t1.d1, (select top 1 d1                                          dbo.tablename t2                                         t2.id < t1.id                                                , d1 not null                                        order  id desc)),                  d2 = coalesce(t1.d2, (select top 1 d2                                          dbo.tablename t2                                         t2.id < t1.id                                               , d2 not null                                        order  id desc)),                  d3 = coalesce(t1.d3, (select top 1 d3                                          dbo.tablename t2                                         t2.id < t1.id                                              , d3 not null                                        order  id desc))             dbo.tablename t1)  update t  set    t.d1 = c.d1,         t.d2 = c.d2,         t.d3 = c.d3    dbo.tablename t         inner join cte c                 on t.id = c.id   t.d1 null          or t.d2 null          or t.d3 null  

demo

edit since have mentioned in comments have '' instead of null, here modified version supports both:

with cte          (select t1.id,                  t1.date,                  d1 = case when coalesce(t1.d1, '') <> '' d1                         else(select top 1 d1                                dbo.tablename t2                               t2.id < t1.id                                     , coalesce(t2.d1, '') <> ''                              order  t2.id desc) end,                  d2 = case when coalesce(t1.d2, '') <> '' d2                         else(select top 1 d2                                dbo.tablename t2                               t2.id < t1.id                                     , coalesce(t2.d2, '') <> ''                              order  t2.id desc) end,                  d3 = case when coalesce(t1.d3, '') <> '' d3                         else(select top 1 d3                                dbo.tablename t2                               t2.id < t1.id                                     , coalesce(t2.d3, '') <> ''                              order  t2.id desc) end             dbo.tablename t1)  update t  set    t.d1 = c.d1,         t.d2 = c.d2,         t.d3 = c.d3    dbo.tablename t         inner join cte c                 on t.id = c.id   coalesce(t.d1, '') = ''          or coalesce(t.d2, '') = ''          or coalesce(t.d3, '') = ''  

demo