SQL Where with next joins

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.

--------------Solutions-------------

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.

You will need to name your nested select so you are able to filter with the where clause.

(Select calc_distance(lat1,lon1,lat2,lon2) as distance, f2.src_airport, f2.dest_airport
...) table2
WHERE
f1.src_airport = table2.src_airport
AND
f1.dest_airport = table2.dest_airport;

Category:sql Time:2018-05-09 Views:0

Related post

  • how to convert sql query with inner join to linq lambda expression? 2012-04-20

    i want to select some fields from some tables please help to convert sql query to linq , i need to show these values into mvc3 webgrid. How to convert inner join into linq? Or is there any way to do it in EF? SELECT DISTINCT SecurityIdentifier_All.Se

  • Optimising SQL Query With Multiple Joins 2009-11-03

    I have the following three tables: Venues, Events, Instance. Events have a Venue (Venues have many events), and Events have many Instances. For example, the Event "Terminator2" is a film that has a certain cinema as its Venue and will have many insta

  • SQL views with right joins 2011-12-19

    I have to create a view to show an overview of orders in my database. The overview doesn't need the order items, just the order id, order date, customer name, order total and order status. In the database, there are 2 tables with customer details (on

  • How to convert Sql query with inner join statement to sql query with Where statement(no inner join in statement) 2009-12-05

    I have generated the following sql query code in access using the Qbe and converting it to sql.However, i want proof that i did without help so i intend to take out all inner join statements and replace them with the WHERE statement. How do i work it

  • sql tuning - with multiple joins 2010-09-06

    I recently read somewhere that one of ways of tuning SQL query is that if it has too many joins, then do one join with fewer tables and cache the results in a temporary table, then do the rest of the query joining on that table. My question is how it

  • Sybase SQL update with self-join 2011-01-05

    What's the right syntax in Sybase SQL to do an update with a self join? E.g. assuming you have the below table (#tmptbl): account | client |amount | date ------------------------------------- ACT1 | CLIENTA | 12 |2010-12-30 ACT2 | CLIENTB | 5 |2010-1

  • How to add a row to the result of a SQL query with INNER JOIN? 2011-04-11

    In times past, when I need to add a row to the result of a SQL statement, I write a statement like this: SELECT colA, colB FROM my_table UNION SELECT 'foo' AS colA, 'bar' as colB; However, suppose I've written the following SQL: SELECT t1.colA, t1.co

  • Ambiguous column name SQL error with INNER JOIN: Why? 2011-05-11

    The following code will work to select data from two tables: SELECT t1.foo, t2.bar FROM TABLE1 t1 INNER JOIN TABLE2 t2 ON t1.foo=t2.foo I could just as easily written SELECT t2.foo, t2.bar FROM TABLE1 t1 INNER JOIN TABLE2 t2 ON t1.foo=t2.foo t1.foo o

  • How to change these SQL statements with multiple joins into ActiveRecord style queries? 2011-07-01

    Artists have_many :posts, Posts have_many :comments. Artists follow other artists that they like through "inspirations". (I want to build a facebook-type news-feed to show recent activity such as:) someone has posted a new comment on one of my posts

  • Convert advanced SQL query with nested joins to Linq-to-sql 2012-01-17

    I have ran into a snag with my Linq-to-Sql. I have a sql query that runs the way I want and usually I use Linqer to convert to Linq to see the general idea. But this time my SQL query seems to advanced for Linqer. :/ I think the problem is the INNER

  • Linq version of SQL query with multiple joins and group bys 2012-03-06

    In an attempt to write this query from a legacy database in linq, I am running into performance issues. Is there a better way to write the below sql query in linq. The sql query takes less than 2 seconds to run, whereas the current linq expression ta

  • SQL Server with left join/having sum/group by 2012-04-02

    Table B holds planned values. Table M hold actual values. I need to find all rows in table B where either there is no actual values (ie. joined) row in table M, or where joined rows have different total actual value rows. I am trying a combination of

  • SQL update with a join? 2009-04-08

    I have two tables. One is simple string/ID look up: StrTable: str_key String 0 'a' 1 'b' where the strings are unique. The other is more complex, and includes the shared string_id ValTable: str_key other_key val 0 0 1.234 0 1 1.567 1 0 1.890 Now, I w

  • SQL statement with several joins 2009-08-12

    I must be honest and tell you that I am not good at database queries and this question is probably quite simple. I have three tables Post ID entry Category ID name CategoryBinding ID postID categoryID My normal query is to get all posts with the cate

  • SQL Grouping with multiple joins combining results incorrectly 2010-03-30

    Hi I'm having trouble with my query combining records when it shouldn't. I have two tables Authors and Publications, they are related by Publication ID in a many to many relationship. As each author can have many publications and each publication has

  • SQL Query with conditional JOIN 2010-04-13

    The scenario: Table1 CatId|Name|Description Table2 ItId|Title|Date|CatId (foreign key) I want to return all rows from Table1 and Title,Date from Table2, where The returned from Table 2 must be the Latest one by the date column. (in second table there

  • SQL INSERT with Inner Join 2011-04-03

    I Have a Table (Accommodation) with All information about a holiday home. Inc ID Name etc. I have another Table (Schedule) with a Schedule ID, Date and Price I have a final table to join the two (SchdAccom) with A schdaccom id, the AccomodationID and

  • How to translate SQL statement with multiple join conditions based on subquery to LINQ 2011-07-13

    This might be one of those situations where plain SQL commands are better than LINQ. Here's a simplified version of the SQL statement I'm trying to translate: SELECT * FROM IDTable AS idt INNER JOIN NameTable AS nt ON nt.IDTableID=idt.Id AND nt.Id= (

  • sql query with self join 2011-08-05

    Given a table (TableA) that contains the following data; Id Date Status RecordId 1 01/06/11 2 REC001 2 01/06/11 2 REC002 3 01/06/11 2 REC003 4 01/07/11 1 REC001 How can I return all records with a status of 2 except records with a given RecordId wher

  • SQL Count with inner joins 2011-10-24

    I have this query SELECT semester, COUNT(id) AS total, RIGHT(RTRIM(semester), 4) AS year FROM TextbookReservation WHERE (semester IS NOT NULL) AND (semester <> '') AND (semester <> 'SM2008') AND semester <> 'SU2008') GROUP BY semest

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 0.626 (s). 13 q(s)