sql查询更新INNER JOINED表

Question

我在我的 2 个表中使用 INNER JOIN，即 GuestInfo 和 Reservation

select  RoomNo, DateStart, DateEnd, GuestFName, GuestMName, GuestLName
from GuestInfo inner join Reservation
on GuestInfo.GuestID = Reservation.GuestID

现在，我想更新那个 INNER JOINED TABLE

update (select  RoomNo, DateStart, DateEnd, GuestFName, GuestMName, GuestLName
from GuestInfo inner join Reservation
on GuestInfo.GuestID = Reservation.GuestID) set EndDate = '1/20/2014'
where GuestFName = 'JAKE'

但是这个查询是错误的。这可能吗？

Answer 1

根据您的数据库，您应该能够使用与此类似的东西。

SQL服务器:

update r
set r.DateEnd = '2014-01-20' 
from Reservation r
inner join GuestInfo g
    on g.GuestID = r.GuestID 
where g.GuestFName = 'JAKE'

MySQL:

update Reservation r
inner join GuestInfo g
    on g.GuestID = r.GuestID 
set r.DateEnd = '2014-01-20' 
where g.GuestFName = 'JAKE'