Suppose I have the following table structure:
a (a_id number primary key, a_code varchar unique not null)
b (b_id number primary key, a_id number foreign key references a(a_id), b_value varchar
I need to insert on table b a couple hundred pairs of (a_id, b_value) if they do not already exist, and the information I have is (a_code, b_value). I'm coming from a Postgres background where doing this would be trivial, but I'm having trouble finding a way of pulling it off in Oracle without resorting to creating and subsequently dropping a materialized view, or doing one insert per row.
This was the least ugly solution I was able to come up with:
insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(b_value, iun_b_value) */
into b (b_id, a_id, b_value)
select b_id.nextval, a_id, b_value
from a
inner join
(select 'code1' a_code, 'value1' b_value from dual union all
select 'code2' a_code, 'value2' b_value from dual union all
select 'code3' a_code, 'value3' b_value from dual union all
select 'code4' a_code, 'value4' b_value from dual) new
on a.a_code = new.b_value
But I was told I can't use that semantic hint for handling duplicate rows on production, so I tried this:
merge into b using
(select a_id, b_value
from a inner join
(select 'code1' a_code, 'value1' b_value from dual union all
select 'code2' a_code, 'value2' b_value from dual union all
select 'code3' a_code, 'value3' b_value from dual union all
select 'code4' a_code, 'value4' b_value from dual) new
new on a.a_code = new.a_code) insert_codes
on (insert_codes.a_code = b.a_code and insert_codes.b_value = b.b_value)
when not matched then
insert (b.b_id, b.a_id, b.b_value)
values (b_id.nextval, insert_codes.a_id, 1, insert_codes.b_value)
But I get the same "ORA-00980: synonym translation is no longer valid" error I was getting when I was referencing an alias in a subselect while trying to solve this using a where not exists clause.
Is there any way to do it without materialized views or multiple inserts?
Looking at my simplified example made me realize what was wrong. The on clause was
on (insert_codes.a_code = b.a_code and insert_codes.b_value = b.b_value)
but it should be
on (insert_codes.a_id = b.a_id and insert_codes.b_value = b.b_value)
So this works:
merge into b using
(select a_id, b_value
from a inner join
(select 'code1' a_code, 'value1' b_value from dual union all
select 'code2' a_code, 'value2' b_value from dual union all
select 'code3' a_code, 'value3' b_value from dual union all
select 'code4' a_code, 'value4' b_value from dual) new
new on a.a_code = new.a_code) insert_codes
on (insert_codes.a_id = b.a_id and insert_codes.b_value = b.b_value)
when not matched then
insert (b.b_id, b.a_id, b.b_value)
values (b_id.nextval, insert_codes.a_id, 1, insert_codes.b_value)
Posting here in case someone else has trouble pulling off the same kind of insert in the future.