Search code examples
sql-serversql-updateambiguous

Why is my SQL UPDATE syntax giving 'ambiguous column name'?

I am using SQL Server 2014 and I am running the following UPDATE query:

UPDATE ReservationStay 

SET ReasonForstayCode = b.ReasonForStayCode

FROM MissingReasonForStay b

WHERE ReservationStayID = b.ReservationStayID

The objective of the query is to update the column called 'ReasonForStayCode' in 'ReservationStay' Table using data from 'MissingReasonForStay' table. The look-up needs to be done using the 'ReservationStayID' columns of both tables.

Problem is that SSMS is underlining ReservationStayID at Line 4 of my code and when I run the query I get the message: Ambiguous column name 'ReservationStayID'

What is wrong with my code?

Solution

  • That is because the ReservationStayID in your WHERE clause is not qualified and SQL Server is unable to decide which table that column belongs to.

    Try this

    UPDATE a
SET ReasonForstayCode = b.ReasonForStayCode
FROM MissingReasonForStay b
INNER JOIN ReservationStay a
ON a.ReservationStayID = b.ReservationStayID