inserting data from another table

Question

create user TESTING identified by "Test";

grant connect, resource to TESTING;
grant dba to TESTING; 
connect TESTING/Test;
set define off;
create table airport_final
(
iata_code varchar2(3),
city_code varchar2(3),
city_name nvarchar2(24),
country_code varchar2(2),
country_name nvarchar2(33),
no_of_airports number(1),
airport_code varchar2(3),
city_with_country nvarchar2(61),
airport_name nvarchar2(80)
);
INSERT INTO airport_final(iata_code, city_code, city_name, country_code, country_name) 
VALUES ('YVE','YVE', 'Vernon', 'CA', 'Canada');
INSERT INTO airport_final(iata_code, city_code, city_name, country_code, country_name) 
VALUES ('ZZW','ZZW', 'Day Trip Mystery', 'PG', 'Australia');
INSERT INTO airport_final(iata_code, city_code, city_name, country_code, country_name) 
VALUES ('VRA','VRA', 'Varadero', 'CU', 'Cuba');
INSERT INTO airport_final(iata_code, city_code, city_name, country_code, country_name) 
VALUES ('VRO','VRA', 'Varadero', 'CU', 'Cuba');
INSERT INTO airport_final(iata_code, city_code, city_name, country_code, country_name) 
VALUES ('BOL','BOL', 'Ballykelly', 'PG', 'United Kingdom');
INSERT INTO airport_final(iata_code, city_code, city_name, country_code, country_name) 
VALUES ('BMV','BMV', 'Buon Ma Thuot', 'PG', 'Vietnam');
INSERT INTO airport_final(iata_code, city_code, city_name, country_code, country_name) 
VALUES ('WBI','WBU', 'Boulder', 'US', 'United States');
INSERT INTO airport_final(iata_code, city_code, city_name, country_code, country_name) 
VALUES ('WBU','WBU', 'Boulder', 'US', 'United States');
INSERT INTO airport_final(iata_code, city_code, city_name, country_code, country_name) 
VALUES ('WHH','WBU', 'Boulder', 'US', 'United States');
commit;
select iata_code, city_code, city_name, country_code, country_name from airport_final where city_code='VRA';
create table city(
city_code varchar2(3),
country_code varchar2(3),
city varchar2(24)
);

I have inserted data from CSV file to oracle table airport_final and want to insert particular columns into table city by using this statement

SQL> insert
 into city(city_code, country_code,city)
 with t as (
 select distinct c.country_code,
 a.city_name,
 a.city_code
 from airport_final a,
 city c
 where c.city_code = a.city_code
 )
 select 
 city_code,
 country_code,
 city_name
 from t
/
 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 
0 rows created.

This statement is not inserting data Please advise how can I insert specific column(S) data from another table

Best Regards

• Paul W: You are joining to city in your query but you haven't populated it yet. No rows can come out of t. As I mentioned in your other post, you are going to have to clean this data up manually. Take your airport data, pop it into Excel, and manually resolve conflicting city_code/country_code/city_name assocations. When its' clean, script insert statements and populate city.

Answer 1

Your insert query uses join between these two tables, which suggests that there already might exists some rows in the target, city table. If that's so, how about another approach - merge?

If there already are some rows, perform update; otherwise, perform insert:

SQL> merge into city a
 2 using (select distinct f.city_code, f.country_code, f.city_name
 3 from airport_final f
 4 ) b
 5 on (a.city_code = b.city_code)
 6 when matched then update set
 7 a.country_code = b.country_code,
 8 a.city = b.city_name
 9 when not matched then insert
 10 (city_code, country_code, city) values
 11 (b.city_code, b.country_code, b.city_name);
6 rows merged.
SQL> select * from city;
CITY_CODE COUNTRY_CODE CITY
---------- --------------- ------------------------
ZZW PG Day Trip Mystery
VRA CU Varadero
YVE CA Vernon
WBU US Boulder
BMV PG Buon Ma Thuot
BOL PG Ballykelly
6 rows selected.
SQL>

Answer 2

What's wrong with [the old-fashioned]:

insert into city( city_code, country_code, city )
select [distinct] 
 a.city_code
, c.country_code
, a.city_name
from airport_final a
inner join city c
 on a.city_code = c.city_code

• Raakh: select [distinct] * ERROR at line 1: ORA-00936: missing expression
• Raakh: Throwing error on select [distinct]
• Phill W.: Either remove the square braces around the keyword distinct or, better, still get rid of it completely because, IMHO, using "select distinct" almost always indicates a problem the the source data [or data structures].
• Raakh: When I remove square braces then its taking duplicate records and by removing braces the query produces same results that I posted in my question
• Phill W.: "Duplicate" in what respect? distinct a, b, c is effectively the same as group by a, b, c order by a, b, c (and performs about as "well"). If you want to remove duplication within a single column, you need to explain further - and, again, suggests a problem with your table structures.