0

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

1
  • 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.
    – Paul W
    Dec 12, 2023 at 12:33

2 Answers 2

0

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>
0

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
5
  • select [distinct] * ERROR at line 1: ORA-00936: missing expression
    – Raakh
    Dec 11, 2023 at 12:05
  • Throwing error on select [distinct]
    – Raakh
    Dec 11, 2023 at 12:06
  • 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].
    – Phill W.
    Dec 11, 2023 at 12:07
  • 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
    – Raakh
    Dec 11, 2023 at 13:18
  • "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.
    – Phill W.
    Dec 11, 2023 at 15:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.