Início / dba / Perguntas / 333928
Accepted
Raakh
Asked: 2023-12-11 18:04:23 +0800 CST

inserindo dados de outra tabela

  • 772

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

2 respostas

  1. O que há de errado com [o antiquado]:

    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
    • 0
  2. Best Answer

    Sua consulta de inserção usa junção entre essas duas tabelas, o que sugere que já podem existir algumas linhas na citytabela de destino. Se for assim, que tal outra abordagem merge?

    Se já existirem algumas linhas, execute a atualização; caso contrário, execute 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

relate perguntas