0

So I am trying to update the values of one table based on the values of another. I am confused as to how to use UPDATE statement with inner join as I require the use of mutilple tables to update a value. Here is what I did.

UPDATE(SELECT FLIGHT.A_SEATS, FLIGHT.B_SEATS FROM TABLE FLIGHT 
INNER JOIN TABLE BOOKING 
ON BOOKING.FLIGHTID=FLIGHT.FLIGHTID 
WHERE BOOKING.BOOKINGID=:NEW.BOOKINGID)
FLIGHT.B_SEATS=FLIGHT.B_SEATS+v_booked, 
FLIGHT.A_SEATS=FLIGHT.A_SEATS-v_booked;
Sas
  • 278
  • 1
  • 12
Maryam zia
  • 19
  • 4
  • You're nearly there. You're using the aliases from your sub-query rather then aliasing the sub-query and using the values in there to do your update. See the second half of the top answer in the linked question. – Ben Apr 02 '18 at 10:07
  • @Ben yes I read the answers in that post. Thats actually what I used to make this update statement but idk where I am messing up.. – Maryam zia Apr 02 '18 at 10:10
  • Alias your sub-query to something, then replace the aliases in your `set` clause with the alias in your sub-query. You're also missing the `set` clause... `UPDATE(SELECT FLIGHT.A_SEATS, FLIGHT.B_SEATS FROM TABLE FLIGHT INNER JOIN TABLE BOOKING ON BOOKING.FLIGHTID=FLIGHT.FLIGHTID WHERE BOOKING.BOOKINGID=:NEW.BOOKINGID) blah set blah.B_SEATS=blah.B_SEATS+v_booked, blah.A_SEATS=blah.A_SEATS-v_booked;` – Ben Apr 02 '18 at 10:11
  • The `table` keyword is for casting collections to relational tables. It doesn't belong here. (Also `inner` is valid but redundant, because `join` means the same thing as `inner join`. I always feel that less is more and I leave it out.) – William Robertson Apr 02 '18 at 10:28
  • 1. Where is the keyword `SET`, which is required in the `UPDATE` statement? 2. What is `v_booked`? You are joining to table `BOOKING` - is `v_booked` the name of a column in that table? If yes, that's an odd name for a column. If not, why are you joining to that table in the first place? 3. Is `FLIGHTID` primary key, or at least unique, in `BOOKING`? If it isn't, then the update through join will fail. –  Apr 02 '18 at 13:38

0 Answers0