CODEWITHSUNDEEP
sql
Michael Metreveli
Michael Metreveli

Reputation: 111

SQL code can't see a certain table

insert into airports(country_id)
select country_id 
from countries 
where countries.country_name =  flight_all.dep_country

This simple code can't compile because sql doesn't see flight_all table. how can I make it visible (without any extra 'from' keywords) ?

Upvotes: 0

Views: 34

Answers (2)

David
David

Reputation: 218960

Because flight_all isn't defined in your query. You need to identify your tables in the FROM clause. Using your current approach, it would look something like this:

select country_id 
from countries, flight_all
where countries.country_name = flight_all.dep_country

However, note that this syntax is considered fairly sloppy (and perhaps even deprecated depending on the RDBMS). Instead, use a JOIN instead of a WHERE clause to perform your JOIN logic. Something like this:

select country_id 
from countries
inner join flight_all
on countries.country_name = flight_all.dep_country

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

You should google SQL JOIN

You probably want DISTINCT to avoid insert duplicated

insert into airports(country_id)
select distinct country_id 
from countries
join flight_all
  ON countries.country_name =  flight_all.dep_country

Upvotes: 3

Related Questions