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

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.

Answer 2

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

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