oracle insert into column using subquery

Question

I want to insert data into a column in the table.

Table a
ID  col1  col2
1    A    null  
2    B    null

Table b
ID  col1 
1    C
2    D

Expected results:

Table A
ID   col1  col2
1     A     C
2     B     D

I tried this:

insert into tableA (col2) 
select b.col1
from tableB b , tableA a
where b.id = a.id

and I received

0 row inserted.

How do I insert the col1 in B into col2 in A for the matching 'id' columns?

Thank you.

Answer 1

You must use Merge statement when inserting based on joins. Also in table tableA col2 already exist but you want to insert a value on join then you must update that column.

merge into tablea a using tableb b on (b.id = a.id) when matched then update set a.col2 = b.col1;

Answer 2

What you want to do shouldn't require a subquery. I'm not a huge fan of the table a, table b notation, try this:

update a
set col2 = b.col1
from tableB b 
join tableA a
on a.id = b.id