SQL Where with next joins

Question

I'm trying to update a table with a function and running into some issues with my update syntax,was wondering if anyone could help point where I am going wrong.

My flights table:

    SQL> describe flights
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------

 AIRLINE                                            VARCHAR2(3)
 AIRLINE_ID                                         VARCHAR2(5)
 SRC_AIRPORT                                        VARCHAR2(4)
 SRC_AIRPORT_ID                                     VARCHAR2(5)
 DEST_AIRPORT                                       VARCHAR2(4)
 DEST_AIRPORT_ID                                    VARCHAR2(5)
 CODESHARE                                          CHAR(1)
 STOPS                                              NUMBER(38)
 EQUIPMENT                                          VARCHAR2(50)
 DISTANCE                                           NUMBER

My select statement with function works no problem:

SELECT SRC,DEST,calc_distance(lat1,lon1,lat2,lon2)as dist 
FROM (Select flights.src_airport as SRC, airports1.Latitude as lat1,
airports1.longitude as lon1,flights.dest_airport as DEST, airports2.Latitude as lat2,
airports2.longitude as lon2 FROM airports airports1 JOIN
flights
ON airports1.iata_faa = flights.src_airport
JOIN
airports airports2
ON airports2.iata_faa = flights.dest_airport);

Where I am having trouble is updating the flights table to include the distance calculation from this select into the appropriate tuple in flights. What I want to do is check that the source and destination airports from the select statement are equal to the src and destination airports from the flights table and insert accordingly, but I keep getting issues when writing this statement.

So far I have:

UPDATE flights f1
SET distance = 
    (
    SELECT DISTINCT calc_distance(lat1,lon1,lat2,lon2)
    FROM 
        (Select f2.src_airport as SRC, airports1.Latitude as lat1,
        airports1.longitude as lon1,f2.dest_airport as DEST, airports2.Latitude as lat2,
        airports2.longitude as lon2 FROM airports airports1 
        JOIN
        flights f2
        ON airports1.iata_faa = f2.src_airport
        JOIN
        airports airports2
        ON airports2.iata_faa = f2.dest_airport
        )
    )
    WHERE
        f1.src_airport = f2.src_airport
        AND
        f1.dest_airport = f2.dest_airport;

But keeping getting the error:

ERROR at line 20:
ORA-00904: "F2"."DEST_AIRPORT": invalid identifier

Anyone have any ideas how I could fix this, I think the issue is with using a where statement outside of the joins.

Answer 1

Ah I finally got it!!

I had to keep the SRC and DEST inside of my inner select statement so that I could name and reference them in the where clause, but then nest that whole thing inside another select so I could choose out ONLY the distance to update with. Answer is here:

UPDATE flights f1
SET distance = 
(
    SELECT T.dist
    FROM (
            SELECT DISTINCT SRC,DEST,calc_distance(lat1,lon1,lat2,lon2)as dist 
            FROM (Select flights.src_airport as SRC, airports1.Latitude as lat1,
            airports1.longitude as lon1,flights.dest_airport as DEST, airports2.Latitude as lat2,
            airports2.longitude as lon2 FROM airports airports1 JOIN
            flights
            ON airports1.iata_faa = flights.src_airport
            JOIN
            airports airports2
            ON airports2.iata_faa = flights.dest_airport)
        )T  
    WHERE
    T.SRC = f1.src_airport
    AND
    T.DEST = f1.dest_airport    
);

Thanks for the help, I think @RLS was very close.