'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?

sql-serversql-updateambiguous


Solution 1:[1]

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

Solution 2:[2]

Could you try this:

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

Solution 3:[3]

i know this is an old question but figured i would post the solution since i don't see it here. you were almost there and correct about not being able to alias the table name in the update statement, so the solution is to just use the entire table name, using an alias is just to shorten things so you don't have to use the entire table name when writing queries anyhow.

    UPDATE ReservationStay 
    SET ReservationStay.ReasonForstayCode = b.ReasonForStayCode
    FROM MissingReasonForStay b
    WHERE ReservationStay.ReservationStayID = b.ReservationStayID

i believe this should work without having to change column names in either table.

Solution 4:[4]

UPDATE ReservationStay a
SET ReasonForstayCode = b.ReasonForStayCode
FROM MissingReasonForStay b
WHERE a.ReservationStayID = b.ReservationStayID

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Raj
Solution 2 gotqn
Solution 3 Raymond
Solution 4 Jeremy Caney

Related Questions