SQL Server ambigous col name

Question

this is concerning SQL Server Management

I have been getting this error when trying to update a table: "Ambiguous column name 'Id'."

this is the query:

update DispatcherDB.dbo.Engineer
set MobilePhone = RestoreDB.dbo.W6ENGINEERS.MobilePhone
from DispatcherDB.dbo.Engineer join RestoreDB.dbo.W6ENGINEERS
on Id = W6Key

I have done this query before on other tables without a problem. I'm still a novice when it come to SQL, so any help/advice would be greatly appreciated! Thanks!

You need to tell the ON clause which Id column you mean (there is obviously one in both tables - and what a horrible name for a column anyway). Also please see [this](http://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server/1604212#1604212). — Aaron Bertrand, Sep 25 '14 at 13:27
You have the `id` field in the tables you use in this update, you need to add either an alias or table name — neshkeev, Sep 25 '14 at 13:27

score 2 · Accepted Answer · edited Sep 25 '14 at 13:40

2

You have to give aliases to your tables (not really have to, but nice to do) and then you have to define of which table the id you are using in the on clause.

update E
set MobilePhone = W.MobilePhone
from DispatcherDB.dbo.Engineer E join RestoreDB.dbo.W6ENGINEERS W
on E.Id = W.W6Key

edited Sep 25 '14 at 13:40

Aaron Bertrand

272,866
37
466
490

answered Sep 25 '14 at 13:28

Giannis Paraskevopoulos

18,261
1
49
69

Shouldn't it be `UPDATE E` not `UPDATE Dispatcher...`? You can also use the W alias in the second line... – Aaron Bertrand Sep 25 '14 at 13:29
The whole point of using an alias though is to not have to repeat `database.schema.table` throughout the query. I like the answer (+1) it just seems like it's arguing with itself: "Aliases are good!" "But I don't want to use an alias!" – Aaron Bertrand Sep 25 '14 at 13:31
@AaronBertrand I would have say "Yes, this is why i use it in the on clause", but that would have sounded silly as i do not use it in the update!!! :-) – Giannis Paraskevopoulos Sep 25 '14 at 13:32
I can scream on the internet too! – Kermit Sep 25 '14 at 13:34
@AaronBertrand Thanks for the edit...i totally missed that, as it was mostly a copy and paste from the question's code. – Giannis Paraskevopoulos Sep 25 '14 at 13:42
Well I did mention it in my first comment. :-) – Aaron Bertrand Sep 25 '14 at 13:43
@AaronBertrand Indeed, but i edited only the `Update DispatcherDB.dbo.Engineer`. – Giannis Paraskevopoulos Sep 25 '14 at 13:43

SQL Server ambigous col name

1 Answers1