未链接的两个表之间的交互
未链接的两个表之间的交互
提问于 2016-03-25 06:51:33
我有以下三个表：
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)
)正如您所看到的，机票和机票都有一个名为"Price“的列。请注意，连接航班和机票的表是中途停留表。ID_Stopover是机票中的FK，ID_Flight是中途停留的FK。我在这里的目标是以某种方式导入从“价格”栏(航班)到“票价”(机票)的值。就像这样：
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)回答 2
Stack Overflow用户
回答已采纳
发布于 2016-03-25 08:26:45
可以使用合并根据链接表的值更新表：
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.priceStack Overflow用户
发布于 2016-03-25 07:30:07
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/36215382
复制相关文章
相似问题
腾讯云开发者
