我正在使用SQL Server 2014,并且正在运行以下UPDATE查询:
UPDATE ReservationStay
SET ReasonForstayCode = b.ReasonForStayCode
FROM MissingReasonForStay b
WHERE ReservationStayID = b.ReservationStayID
查询的目的是使用"MissingReasonForStay"表中的数据更新"ReserveStay"中名为"ReasonForStayCode"的列。查找需要使用两个表的"ReserveStayID"列来完成。
问题是SSMS在我代码的第4行的ReserveStayID下面加了下划线,当我运行查询时,我得到的消息是:不明确的列名'ReserveStayID'
我的代码出了什么问题?
这是因为WHERE
子句中的ReservationStayID
不合格,SQL Server无法决定该列属于哪个表。
试试这个
UPDATE a
SET ReasonForstayCode = b.ReasonForStayCode
FROM MissingReasonForStay b
INNER JOIN ReservationStay a
ON a.ReservationStayID = b.ReservationStayID
你能试试这个吗:
UPDATE ReservationStay
SET ReasonForstayCode = b.ReasonForStayCode
FROM ReservationStay a
INNER JOIN MissingReasonForStay b
ON a.ReservationStayID = b.ReservationStayID;
我知道这是一个老问题,但我想我会发布解决方案,因为我在这里看不到它。您几乎做到了,并且纠正了不能在update语句中对表名进行别名的错误,因此解决方案是只使用整个表名,使用别名只是为了缩短时间,这样在编写查询时就不必使用整个表名称。
UPDATE ReservationStay
SET ReservationStay.ReasonForstayCode = b.ReasonForStayCode
FROM MissingReasonForStay b
WHERE ReservationStay.ReservationStayID = b.ReservationStayID
我认为这应该可以在不必更改任何一个表中的列名的情况下工作。
UPDATE ReservationStay a
SET ReasonForstayCode = b.ReasonForStayCode
FROM MissingReasonForStay b
WHERE a.ReservationStayID = b.ReservationStayID