两个未链接的表之间的交互

Question

我有以下3个表：

CREATE TABLE Flight
(   
    ID_Flight number (10) not null,
    Status varchar (50) not null,
    Price varchar (10) not null,
    Boarding date,
    PRIMARY KEY (ID_Flight)
)
CREATE TABLE Stopover
(
    ID_Stopover number (10) not null,
    ID_Flight number,
    PRIMARY KEY (ID_Stopover),
    FOREIGN KEY (ID_Flight) REFERENCES Flight (ID_Flight)
)
CREATE TABLE Ticket
(
    ID_Ticket number (10),
    ID_Stopover number,
    Seat varchar (5) not null,
    Price varchar (10) not null,
    PRIMARY KEY (ID_Ticket),
    FOREIGN KEY (ID_Stopover) REFERENCES Stopover (ID_Stopover)
)

如您所见，Flight和Ticket两个表都有一个名为"Price"的列。请注意，作为Flight和Ticket之间链接的表是Stopover表。ID_Stopover是Ticket中的FK，ID_Flight是Stoover。我的目标是以某种方式将Price（Flight）列中的值导入到Price（Ticket）列。类似这样的东西：

ID_Flight -> 1 | Price (Flight) -> $100,99
ID_Flight -> 2 | Price (Flight) -> $350,00
ID_Flight -> 3 | Price (Flight) -> $1000,00
ID_Ticket -> 1 | Price (Ticket) -> $350,00 (same value from ID_Flight 2)
ID_Ticket -> 2 | Price (Ticket) -> $350,00 (same value from ID_Flight 2)
ID_Ticket -> 7 | Price (Ticket) -> $100,00 (same value from ID_Flight 1)

Answer 1

您可以使用合并根据链接表的值更新表：

merge into ticket t
using ( select *
        from stopOver 
          inner join flight 
            using(id_flight)
      ) sub
on (t.ID_Stopover = sub.ID_Stopover)
when matched then
  update set price = sub.price

Answer 2

select * 
from (
select f.id_flight, 
       t.id_ticket, 
       f.price flight_price, 
       t.price ticket_price
from flight f, ticket t, stopover s
where f.id_flight = s.id_flight 
and t.id_stopover = s.id_stopover
)
where id_flight = &x