Copy field into next record 1

[MarvinJean]

I have a table with a column that may be null and may have data.

If the field is null I want to copy from the previous record. The first record will not be null.

Table:

Group Unit Data
123 1 Alpha
123 2 <null>
123 3 <null>
123 4 Beta
123 5 <null>
123 6 Gamma
123 7 <null>
123 8 <null>
123 9 Delta

I want to copy Alpha into unit 2 and 3. Copy Beta into unit 5. Copy Gamma into unit 7 and 8. Do not alter unit 1,4,6, or 9

Thanks

 

[nigelrivett]

while exists (select * from tbl where data is null)
update tbl
set data = t2.data
from tbl
join tbl t2
on t2.unit = tbl.unit - 1
and tbl.data is null
and t2.data is not null

you could also try

declare @s varchar(10)
update tbl
set @s = data = coalesce(data,@s)


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[MarvinJean]

Thank you very much.

I tried your solution and it is charmed.

Thanks again

Marvin Jean

 

[nigelrivett]

Note that

declare @s varchar(10)
update tbl
set @s = data = coalesce(data,@s)

Will depend on the clustered index (probably).
I would use it with caution.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.