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

Issue

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

Answered By - Raj

Answer Checked By - Terry (PHPFixing Volunteer)