CODEWITHSUNDEEP
sqloracle-databaseplsqlrelational-databaseoracle-xe
Jdv
Jdv

Reputation: 993

Oracle INSERT statement with selects on different tables

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.

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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.

Upvotes: 1

Related Questions