CODEWITHSUNDEEP
sqloracle-databasesql-insert
sahadev
sahadev

Reputation: 29

Select from multiple tables, insert into another table Oracle SQL query

I have gone through this link here. I have similar question, but I want data from two different tables. something like this. But it doesn't return the expected results.

INSERT INTO tbl_1 (fld_id1,fld_id2)
    SELECT tbl_2.col1
    FROM tbl_2 WHERE someCondi,
    SELECT tbl_3.col1
    FROM tbl_3 WHERE someCondi

Eg.

Table1:

Col1  Col2  
----------
1      56  
1      57  
1      59

Table2: 

Col1  
----
1

Table3 

col1  
-----
56  
57  
59

Upvotes: 0

Views: 4775

Answers (2)

user330315
user330315

Reputation:

Looks like you are looking for a cross join:

INSERT INTO tbl_1 (fld_id1,fld_id2)
SELECT t2.col1, t3.col1
FROM table3 t3
  cross join table2 t2
WHERE <some condition>

At least with you sample you have shown us, this would return your expected result.

Upvotes: 1

Giacky
Giacky

Reputation: 181

If (fld_id1,fld_id2) are tbl_2.col1 and tbl_3.col1, so you you have to use JOIN

INSERT INTO tbl_1 (fld_id1,fld_id2)
SELECT t2.col1, t3.col1
FROM tbl_2 t2 
INNER JOIN tbl_3 t3 ON "JOIN CONDITION" 
WHERE "t2.someCondi"
AND "t3.someCondi"

So, if you can't use JOIN you can try with Cartesian Product:

INSERT INTO tbl_1 (fld_id1,fld_id2)
SELECT t2.col1, t3.col1
FROM tbl_2 t2, tbl_3 t3
WHERE "t2.someCondi"
AND "t3.someCondi"

Upvotes: 2

Related Questions