Refresh a column with a different value from the same table?

Question

The setup looks like this:

Col1        Col2
12345       12  
12348       14
20145       16
00541       Null
51234       22

Simplified, obviously. What I want to do is update Col2 wherever it is Null, setting it to Col2 so that it has the closest value to Col1 (so in this example the fourth line should have Col2 equal to 12). Here is how I got it:

UPDATE Temp.dbo.Sheet4
   SET Col2 = (SELECT FIRST(Col2) 
                 FROM Temp.dbo.Sheet4 
                WHERE Col2 IS NOT NULL 
             ORDER BY ABS(***Col1 from the outside of this select statement*** - Col1))
WHERE Col2 IS NULL

Probably not so close. But how can I do this? I can’t make out about it. I am also open for this in Excel / Access / independently, but I decided that SQL Server would be the easiest.

Answer 1

It's hard to try this without creating a database, but does it work?

UPDATE sh
   SET sh.Col2 = (SELECT TOP 1 sh_inner.Col2
                 FROM Temp.dbo.Sheet4 sh_inner
                WHERE sh_inner.Col2 IS NOT NULL 
             ORDER BY ABS(sh.Col1 - sh_inner.Col1))
FROM Temp.dbo.Sheet4 sh
WHERE sh.Col2 IS NULL

Answer 2

Martin,

. :

create table #junk
(col1 int, col2 int)

insert #junk
values(12345,12),
(12348,14),
(20145,16),
(541,null),
(51234,22)

update j
    set col2 = (select top 1 j2.col2 from #junk j2 where j2.col2 is not null order by ABS(j.col1-j2.col1))
from #junk j where col2 is null

select * from #junk
drop table #junk