The table is ambiguous

Question

I am trying to create an update-Command in Microsoft SQL Server. The error is

the table "field" is ambiguous.

In the table "Field", I have user-specific Content (like First Name, Mailadress, Phone-Number).

Every Field is on a separate Row, with a separate ID. With the secAcc-ID, I can join to every Field to the users.

The source is a View. I got the Employee-Number in the View, so I have to join the DefinitionID for the employee id.
But I will update the Mailadress, so I have to join the field-Table again, with the correct definition id.

update [ootesting]..[Field]
set [Text] = [matrix].[E-Mail]
from 
[linkedserver] matrix
join [ootesting]..[Field] oofield on matrix.personalnr = oofield.Text and oofield.DefinitionId = 'BEEFE7A8-E679-41D8-AF8D-258AC7757E01'
join [ootesting]..[secacc] oosecacc on oofield.SecurityAccount_Id = oosecacc.Id
join [ootesting]..[Field] oofield2 on oosecacc.id = oofield2.SecurityAccount_Id and oofield2.DefinitionId = 'F96614C2-3B83-455C-92D3-1EC8CF9A8882'
where 
oofield.Text is not null

Can you help to fix it?

Answer 1

The problem is your UPDATE clause: UPDATE [ootesting]..[Field]. In your FROM you reference the same object twice, and alias it as oofield and oofield2. SQL Server doesn't know what instance of that object you want to UPDATE; oofield or oofield2.

Instead, use the alias in the UPDATE clause (I assume oofield here). Also, define your schemas. I assume it should be dbo in all cases:

UPDATE oofield
SET [Text] = [matrix].[E-Mail]
FROM dbo.[linkedserver] matrix --Seems an odd name for a table/view
     JOIN [ootesting].dbo.[Field] oofield ON matrix.personalnr = oofield.Text
                                         AND oofield.DefinitionId = 'BEEFE7A8-E679-41D8-AF8D-258AC7757E01'
     JOIN [ootesting].dbo.[secacc] oosecacc ON oofield.SecurityAccount_Id = oosecacc.Id
     JOIN [ootesting].dbo.[Field] oofield2 ON oosecacc.id = oofield2.SecurityAccount_Id
                                          AND oofield2.DefinitionId = 'F96614C2-3B83-455C-92D3-1EC8CF9A8882'
WHERE oofield.Text IS NOT NULL;