Oracle INSERT statement with selects on different tables

Question

I have two tables table1 and table2. Those tables have unique name and id columns.

I also have a relation / join table, table1_table2 which has the straight-forward colums table1_id and table2_id.

What I want to do is to insert a new relation into table1_table2 knowing the names of the elements in table1 and table2 I want to create a relation for. But I need to get their ids to insert them into table_table2.

What I want is something like that:

insert into table1_table2 values ((select id from table1 where name = 'some_name'), (select id from table2 where name = 'another_name'))

I also tried using

insert into table1_table2 values ((select id from (select id from table1 where name = 'some_name') where rownum=1), (select id from (select id from table2 where name = 'another_name') where rownum=1))

which also didn't work.

I understand I can first extract the ids if necessary but I'd prefer it to be in one statement.

Edit: I've also tried

insert into table1_table2 values (select t1.id, t2.id from table1 t1, table2 t2 where t1.name = 'some_name' and t2.name = 'another_name')

which also didn't work

Example data:

table1
id name
1  foo
2  bar

table2
id name
1  some
2  data

table1_table2
table1.id table2.id
1         1

and now I want to insert

table1.id table2.id
2         2

into table1_table2, but I do only know that the entry in table1 has the name bar and the entry in table2 has the name data.

Some sample data and a clearer view of the table structures might help. Do you want the IDs from each table where the names match - for all names, or a specific one, since you've shown 'name' as fixed? What if a name only appears in one or the other table? — Alex Poole
– Alex Poole, Commented Mar 29, 2017 at 14:58

Gordon Linoff · Accepted Answer · 2017-09-06 01:53:36Z

1

This should work:

INSERT INTO table1_table2 (table1_id, table2_id)
    VALUES ( (SELECT id FROM table1 WHERE name = 'some_name'),
             (SELECT id FROM table2 WHERE name = 'another_name')
           );

However, I would write it as:

INSERT INTO table1_table2 (table1_id, table2_id) SELECT t1.id, t2.id FROM table1 t1 JOIN table2 t2 ON t1.name = 'some_name' AND t2.name = 'another_name';

Note in this case if there is no match in either table, then no row is inserted at all. Using VALUES, a NULL values would be inserted.

edited Sep 6, 2017 at 1:53

answered Mar 29, 2017 at 15:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Oracle INSERT statement with selects on different tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related