How can I update a table with data retrieved by a join on itself ?

Question

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

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.

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.

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 :

    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'

Any ideas on what may be happening, and to fix this? I'm using 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

Answer 1

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

Should work

Answer 2

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.