CODEWITHSUNDEEP
sql
user2116972
user2116972

Reputation: 61

sql query Update INNER JOINED table

i use INNER JOIN in my 2 tables which is GuestInfo and Reservation

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

now, i want to update that 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'

but this query is error. is this possible?

Upvotes: 2

Views: 3044

Answers (3)

Davide Guastalli
Davide Guastalli

Reputation: 46

If I understood correctly you can use:

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270723

The following syntax should work in any database:

update reservation
    set enddate = '1/20/2014'
    where guestid in (select guestId from guestinfo where guestfname = 'Jake')

Upvotes: 2

Taryn
Taryn

Reputation: 247850

You should be able to use something similar to this depending on your database.

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'

Upvotes: 4

Related Questions