1

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?

Charlieface
  • 52,284
  • 6
  • 19
  • 43
Gomoli
  • 27
  • 1

1 Answers1

3

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;
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • Weirdly enough, SQL Server allows you to *not* use the alias and instead use the real name, but *only* if there is a single reference in the top-level scope. – Charlieface Aug 23 '22 at 20:20
  • Yes, it is a bit odd that one. The query engine is *normally* "clever" enough to know you mean the aliased instance of object, but I don't doubt that in older versions of SQL Server (or perhaps SyBase) people wouldn't have been so "lucky". – Thom A Aug 23 '22 at 20:26
  • I would have thought you would *have* to use the alias either way, because once you alias a table you cannot normally refer to it using the original name. There is no other context when this is possible – Charlieface Aug 23 '22 at 20:28