用同一个表中的另一个值更新列? [英] Update column with another value from the same table?

问题描述

设置是这样的:

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

显然是简化了.我想要做的是通过将 Col2 设置为 Col2 值来更新 Col2 为 Null 的任何值，以便在 Col1 中具有最接近的值(因此在此示例中，第 4 行应将 Col2 设置为 12).这是我得到的接近程度:

Simplified, obviously. What I want to do is update Col2 wherever it's Null by setting it to the Col2 value for whatever has the closest value in Col1 (so in this example, row four should have Col2 set to 12). This is how close I've gotten:

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

可能没有那么接近.但是我该怎么做呢?我无法完全理解它.我也愿意在 Excel/Access/任何东西中执行此操作，但我认为 SQL Server 将是最简单的.

Probably not that close. But how can I do this? I can't quite get my head around it. I'm also open to doing this in Excel/Access/whatever, but I figured SQL Server would be the easiest.

推荐答案

在没有设置数据库的情况下尝试这个有点困难，但这行得通吗?

It is kind of hard to try this out without the database set up, but does this 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

这篇关于用同一个表中的另一个值更新列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！