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

Question

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

The question is a bit unclear, at least to me. Could you add some sample data and the result you'd like to get for this sample? — Mureinik
– Mureinik, Commented Feb 16, 2018 at 11:12

Giacky · Accepted Answer · 2018-02-16 11:33:48Z

2

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"

edited Feb 16, 2018 at 11:33

answered Feb 16, 2018 at 11:19

Giacky

1811 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sahadev Over a year ago

There is no relationship between tbl_2 and tbl_3 so can't use join.

user330315 · Accepted Answer · 2018-02-16 11:49:12Z

1

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.

answered Feb 16, 2018 at 11:49

user330315

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related