Find a data from a table and place that data in another ORACLE

Question

Find a data from a table and place that data in another ORACLE

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

I have a table in an Oracle database called temporary

CREATE TABLE temporal(
Nombre varchar(50),
Tipo varchar(50),
Pais varchar(50)
)
------Datos------
----------------------------------
Nombre     |  Tipo      | Pais
----------------------------------
User 1        2           USA
User 2        2           FRANCIA
User 3        1           ALEMANIA
User 4        2           FRANCIA
User 5        1           USA

I have another table called Country

CREATE TABLE PAIS(idpais int primary key,nombre varchar(50))
-------Datos------------
-------------------------
idpais      |   nombre
------------------------
   1              USA
   2             FRANCIA
   3             ALEMANIA

And a table called user that is related to the table Country

CREATE TABLE Usuario(id int primary key,nombre varchar(50),pais int,
FOREIGN KEY(pais) REFERENCES Pais(idpais))

I insert the data of the temporary table in the table Users (id of the user table is autonumeric so I do not need it)

INSERT INTO Usuario(nombre,pais)
SELECT  DISTINCT
t.nombre,

--Aqui es donde tengo el problema al insertar el pais

FROM temporal t
WHERE t.Nombre NOT IN (SELECT nombre FROM Usuario);

I want to insert the idpais but I only have the name of the country

Select idpais From Pais Where nombre=t.pais

I would like the int that results from that query to be able to insert it in the User table

sql oracle insert select

asked by Oscar Corleto Soto 03.01.2019 в 08:02

source

2 answers

Nest several tables to show records with the most recent date search for an element in a result of a table

score 0 · Answer 1

You can make a join to the pais table in select , in the line of:

INSERT INTO Usuario(nombre, pais)
SELECT DISTINCT
         t.nombre
       , p.idPais
  FROM temporal t
       inner join Pais p on p.nombre = t.pais
 WHERE t.Nombre NOT IN (SELECT nombre FROM Usuario);

score 0 · Answer 2

The solution I see is to put the user first and then make an UPDATE to enter the country of the same. So like this:

UPDATE Usuario SET                                                                 
pais=(SELECT idpais FROM PAIS WHERE PAIS.nombre=t.pais)         
WHERE Usuario.nombre=t.nombre;