Início / user-159655

Raakh's questions

Martin Hope
Raakh
Asked: 2023-12-11 18:51:58 +0800 CST
  • 5

Estou usando esta instrução para inserir registros de outra tabela:

SQL> insert into city(city_code, country_code, city)
select distinct(city_code), country_code, city_name
from airport_final;

  2    3  
9012 rows created.

Encontrei muitos registros duplicados:

SQL> SELECT city_code, COUNT(city_code)
FROM airport_final
GROUP BY city_code
HAVING COUNT(city_code) > 1;

VRA        2
WBU        3
YMQ        4
YPR        2

330 rows selected.

Não sei se estou selecionando registros distintos, mas por que está inserindo duplicatas

Atenciosamente

oracle
Martin Hope
Raakh
Asked: 2023-12-11 18:04:23 +0800 CST
  • 5

criar usuário TESTING identificado por “Teste”;

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)
);

Eu inseri dados do arquivo CSV na tabela Oracle airport_finale desejo inserir colunas específicas na tabela city usando esta instrução

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.

Esta declaração não está inserindo dados Por favor, informe como posso inserir dados de colunas específicas de outra tabela

Atenciosamente

oracle