sql查询更新INNER JOINED表
我在我的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'
但是这个查询是错误的。 这可能吗？
您应该能够使用类似于此的取决于您的数据库。
SQL Server：
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'
以下语法应该适用于任何数据库：
update reservation
set enddate = '1/20/2014'
where guestid in (select guestId from guestinfo where guestfname = 'Jake')
如果我理解正确，你可以使用：
update reservation
set EndDate = '1/20/2014'
where GuestID in (select GuestID
from GuestInfo inner join Reservation
on GuestInfo.GuestID = Reservation.GuestID
and GuestFName = 'JAKE')