Copy oracle table data from two tables to another table

Question

Here are my tables:

create table country( country_id number(5) primary key, country varchar2(36) );

    create table city(
    city_id number(5) primary key,
    country_id number(5) constraint city_country_fk references country(country_id) NOT NULL,
    city varchar2(36)
    );

SQL> desc airportdemodata;
 Name                    Null?    Type
 ----------------------------------------- -------- ----------------------------
 AIRPORT_ID                  NOT NULL NUMBER(5)
 IATA_CODE                        VARCHAR2(3)
 CITY                         VARCHAR2(36)
 COUNTRY                      VARCHAR2(36)
 AIRPORT                      VARCHAR2(58)

I inserted countries from airportdemodata to country table as under:

INSERT INTO country (country)
SELECT unique(country) from airportdemodata;

it worked fine.
Now I am trying to copy airportdemodata.city into city.city(country_id,city) by matching airportdemodata.country with country.country. I tried like this:

SQL> SELECT a.unique(city), b.country_id FROM airportdemodata a, country b where a.country=b.country;
SELECT a.unique(city), b.country_id FROM airportdemodata a, country b where a.country=b.country
         *
ERROR at line 1:
ORA-01747: invalid user.table.column, table.column, or column specification

Answer 1

Right; inventing your own syntax usually leads to the outcome you experienced. Did you mean to use distinct?

SELECT DISTINCT a.city,
                b.country_id
FROM airportdemodata a
JOIN country b ON a.country = b.country;