SQL Server - Updating Columns with Previous column without Any Key

Question

I have a SQL Server table with five columns:

Field1, Field2,Field3,Field4,Field5

Now I am loading the data from a file, which has Field1 and Field2 value on 1st row, but only field3, field4, field5 in second and third row.

I do not have key columns in the table as of now.

I need to be able to update field1 & field2 of second and third row with the value of 1st row.

This needs to be repeated, for every row without Field1 & Field2, with previous rows.

Please suggest a viable OPTION.

Answer 1

The other solution, which is slower, involves correlated subqueries. Your example was a parent row followed by two child rows, which repeats. This will work regardless of how many child rows there are between the parent rows:

drop table if exists #temp
go

select *
    into #temp
    from (
        values
         (1, 'a1', 'b1', null, null, null)
        ,(2, null, null, 'x1', 'y1', 'z1')
        ,(3, null, null, 'l1', 'm1', 'n1')
        ,(4, null, null, 'i1', 'j1', 'k1')
        ,(5, 'a2', 'b2', null, null, null)
        ,(6, null, null, 'x2', 'y2', 'z2')
        ,(7, null, null, 'l2', 'm2', 'n2')
    ) t(ID, f1, f2, f3, f4, f5)

update #temp
    set 
         f1 = (select top 1 f1 from #temp where f1 is not null and t.ID>=ID order by ID)
        ,f2 = (select top 1 f2 from #temp where f2 is not null and t.ID>=ID order by ID)
    from #temp t

select *
    from #temp
    where f3 is not null
    order by ID