Cathal February 2016

SQL Server 2008 - Ambiguous column name

I am trying to do a join between two tables but I am receiving this error

Ambiguous column name 'NurseWard'

Code:

select 
    @WardID = NurseWard
from 
    dbo.NurseTbl as o
inner join 
    inserted as i on o.NurseID = i.NurseID

Answers


Paul Williams February 2016

This looks like the inserted table in a trigger. The inserted table has the same column names as the original table. From the linked article:

The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.

The query has an alias on both the NurseTbl and inserted tables, and the inner join uses the aliases, but the select does not. You will need to add an alias to the NurseWard column in the select:

select @WardID = o.NurseWard -- or i.NurseWard depending on what you need
from dbo.NurseTbl as o
inner join inserted as i on
o.NurseID = i.NurseID

Post Status

Asked in February 2016
Viewed 1,529 times
Voted 7
Answered 1 times

Search




Leave an answer