sql query Update INNER JOINED table

Question

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?

You can't update the result of an inner join. You can only update one of the base tables. It would greatly help if you would use aliases to show where the columns are coming from. — Gordon Linoff, Feb 27 '13 at 20:13
Also, look at this question: http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql. What RDMS are you using? — Gijs, Feb 27 '13 at 20:13
What is the exact error message? And which DBMS are you using? Oracle? Postgres? — , Feb 27 '13 at 20:19

score 4 · Accepted Answer · answered Feb 27 '13 at 20:14

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'

score 2 · Answer 2 · answered Feb 27 '13 at 20:16

2

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

answered Feb 27 '13 at 20:16

Gordon Linoff

1,242,037
58
646
786

Davide Guastalli · Answer 3 · 2013-02-27T20:25:14.883

0

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

edited Feb 27 '13 at 20:25

answered Feb 27 '13 at 20:17

Davide Guastalli

46
1
5

See the answer from Gordon Linoff, it's better than mine! – Davide Guastalli Feb 27 '13 at 20:18
Query syntax is wrong, When you are using IN clause you cannot return more than one column in subquery – rs. Feb 27 '13 at 20:20

sql query Update INNER JOINED table

3 Answers3

Linked