How to update a table rows with the pattern from first 10 records (similar to what fill handle in ex-CodePudding

I want to update the below table where name is null to repeat the pattern from first 10 rows. enter image description here

sql:

declare @name varchar(255) set @name = select distinct name from #temp update #temp set name = @name where name is not null

Obviously the above the query will not work due to multiple values. I want to update the table where it's null to fill with the pattern from above.

CodePudding user response：

While I agree with Damien's suggestion that you not conflate databases and spreadsheets, you can do this quite easily with window functions and the modulo operator:

WITH x AS 
(
  SELECT dt, 
         name, 
         seq  = ROW_NUMBER() OVER (ORDER BY dt), 
         c    = COUNT(*) OVER()
  FROM #temp
  WHERE name IS NOT NULL
),
y AS 
(
  SELECT dt, 
         name, 
         seq  = ROW_NUMBER() OVER (ORDER BY dt)
  FROM #temp WHERE dt > (SELECT MAX(dt) FROM x)
) 
UPDATE y SET y.name = x.name
FROM x INNER JOIN y
ON x.seq % x.c = y.seq % x.c;

Working example in this fiddle.

This doesn't validate, though, that the non-null values are all contiguous at the beginning - if you need the query to validate that, it's considerably more complicated.

CodePudding user response：

I am just posting an Answer according to your picture.

I think if you want to use it some where else , then you should change it. (It is not General)

update
   t1 
set
   t1.name = t2.name 
from
   (
      select  *,row_number() over (order by dt) - 1 as rn 
      from
         (
            select
               * 
            from
               tbl1 
            where
               name is not null
         )
         as a 
   )
   as t1 
   inner join
      (
         select
            name,
            day,
            mod(rn, 10) as rn 
         from
            (
               select *,row_number() over (order by dt) - 1 as rn 
               from
                  (
                     select
                        * 
                     from
                        tbl1 
                     where
                        name is null
                  )
                  as b
            )
            as tmp 
      )
      as t2 
      on t1.day = t2.day 
      and t1.rn = t2.rn