如何使用连接自身检索的数据更新表？

I have the following data :

我有以下数据：

SectorKey Sector foo  
1         A      null 
2         B      null
...       ...    ...
1         null   a  
2         null   b 
2         null   c 
1         null   d 
2         null   e 
...       ...    ...

I want to update column Sector when it's null based on the value of sectorKey, ie I want Sector to be 'A' when SectorKey is 1 and 'B' when SectorKey is 2

我希望根据sectorKey的值更新列为Sector时的扇区，即当SectorKey为1时我希望Sector为'A'，而当SectorKey为2时我想要'B'

I've tried this query :

我试过这个查询：

update tbFoo
set Sector=A.sector 
from tbFoo A INNER JOIN tbFoo B
ON A.SectorKey=B.SectorKey 
and A.Sector is not null 
and B.Sector is null

and got this error message :

并收到此错误消息：

The table 'tbFoo' is ambiguous.

表'tbFoo'含糊不清。

I've tried to alias the first tbFoo, but it doesn't seem to be a valid syntax. I don't understand why SQLServer complains about an ambiguous naming since I've got all my tables aliased.

我试过别名第一个tbFoo，但它似乎不是一个有效的语法。我不明白为什么SQLServer抱怨模糊的命名，因为我的所有表都有别名。

I've found this thread, and I feel like I'm doing exactly the same thing as in the upvoted answer. I've also tried the query suggested in the accepted answer :

我找到了这个帖子，我觉得我做的事情和upvoted答案完全一样。我也尝试了在接受的答案中建议的查询：

    update tbFoo A
    set Sector = 
       (select Sector from tbFoo 
        where A.SectorKey=SectorKey and Sector is not null)

and then SQLServer complains about an incorrect syntax near 'A'

然后SQLServer抱怨“A”附近的语法不正确

Any ideas on what may be happening, and to fix this? I'm using SQLServer 2008.

关于可能发生的事情的任何想法，并解决这个问题？我正在使用SQLServer 2008。

EDIT I've not shown the total data of my table. I don't have only two cases (A and B), but rather a few thousands of cases. So an explicit case is not an option

编辑我没有显示我的表的总数据。我不只有两个案例（A和B），而是几千个案例。所以一个明确的案例不是一个选择

3 个解决方案

#1

Use the alias in the first part of your update query:

使用更新查询第一部分中的别名：

update B
set Sector=A.sector 
from tbFoo A INNER JOIN tbFoo B
ON A.SectorKey=B.SectorKey 
and A.Sector is not null 
and B.Sector is null

Otherwise it doesn't know which instance of the table to update.

否则，它不知道要更新的表的哪个实例。

#2

Try to use a CTE and change the name of field for alias:

尝试使用CTE并更改别名的字段名称：

WITH CTE_TBFOO(SETOR)
AS
(  
    SELECT Sector
    FROM tbFoo T1  
)
update tbFoo
set Sector= A.SETOR
from CTE_TBFOO A 
WHERE A.SETOR = SectorKey 
and   A.SETOR  is not null 
and   B.Sector is null

#3

update
    tbFoo
set
    Sector = (select tf.Sector from tbFoo tf where
                tbFoo.SectorKey = tf.SectorKey and
                tf.Sector is not null)

Should work

应该管用

#1