0

So I have 3 tables reservations, flight and airport and I need to update price from reservations based on what city it is in(the city field is in airport).

Here are my create tables to show the link between the tables:

create table airport
(airport_code varchar(100) primary key,
city varchar(100),
country varchar(100) );

create table flight
(flight_code varchar(100) primary key, 
dept_airport_code varchar(100),
arr_airport_code varchar(100),
foreign key (dept_airport_code)  references  airport (airport_code),
foreign key (arr_airport_code)  references  airport (airport_code) );

create table reservation
(flight_code varchar(100),
reservation add price integer);

This is what I have tried till now, based on previous answers to similar questions and it is not working.

update reservation r inner join flight f on r.flight_code=f.flight_code
inner join airport a on f.dept_airport_code=a.airport_code
set r.price=4000 where a.city='Dubai';

It's giving me this error: ORA-00971: missing SET keyword

I think this is because Oracle doesn't accept this syntax and those answers were for MySQL.

sania247
  • 33
  • 9

1 Answers1

0

I don't know about Oracle syntax but you can try on the T-SQL way:

update r 
set    r.price=4000
from   reservation 
       inner join flight f on r.flight_code=f.flight_code
       inner join airport a on f.dept_airport_code=a.airport_code
where  a.city='Dubai'

I hope this works!

Happy new year!

Nicolás Sosa
  • 3
  • 3
  • Aargh, I really appreciate your help but it's giving me this error now : ORA-00933: SQL command not properly ended. Happy New Year to you too, Nicolas :) – sania247 Jan 03 '19 at 19:00
  • 1
    @sania247 I thinking and reading about Oracle doesn't support JOIN statements on Update. But if we use a subquery to detect what rows we need to update. For example: update reservation set price = 4000 where r.id in (select r.id from reservation r inner join... ) – Nicolás Sosa Jan 03 '19 at 19:06