This post is inspired by https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9532753900346685083
First, we set up the schema objects as follows:
drop table a; drop table b; create table a ( currency_old varchar(20), currency_indicator_old varchar(20), currency_new varchar(20), currency_indicator_new varchar(20) ); create table b ( currency_old varchar(20), currency_indicator_old varchar(20) ); insert into a values ('GBR','GBRO','LGBR','LGBRO'); insert into a values ('GB1','GBR1','LGB1','LGBR1'); insert into b values ('GBR','GBRO'); commit;
1. Requirement 1
both tables have ‘currency_old’ and ‘currency_indicator_old’ fields,
when ‘currency_old’ and ‘currency_indicator_old’ matches, insert ‘currency_new’ and ‘currency_indicator_new’ into b
This requirement can be achieved by a merge statement:
SQL>@test1 SQL>merge into b 2 using ( select a.* from a join b 3 on a.Currency_old = b.Currency_old 4 and a.Currency_indicator_old = b.Currency_indicator_old) a 5 on ( a.currency_new = b.currency_old 6 and a.currency_indicator_new = b.currency_indicator_old ) 7 when not matched then insert 8 values (a.currency_new, a.currency_indicator_new) 9 ; 1 row merged. SQL> SQL> SQL>select * from b; CURRENCY_OLD CURRENCY_INDICATOR_O -------------------- -------------------- GBR GBRO LGBR LGBRO 2 rows selected.
2. Requirement 2
both tables have ‘currency_old’ and ‘currency_indicator_old’ fields,
when ‘currency_old’ and ‘currency_indicator_old’ matches, update b set its ‘currency_old’ and ‘currency_indicator_old’ to be ‘currency_new’ and ‘currency_indicator_new’ from a
This requirement can be achieved through update a view, but we need to add primary key constraints:
attempt – 1 merge does not work
merge into b using a on ( a.Currency_old = b.Currency_old and a.Currency_indicator_old = b.Currency_indicator_old ) when matched then update set b.Currency_old = a.Currency_new ,b.Currency_indicator_old = a.Currency_indicator_new; ERROR at line 3: ORA-38104: Columns referenced in the ON Clause cannot be updated: "B"."CURRENCY_OLD"
attempt – 2 wthout PK not working
update ( select b.*, a.Currency_new, Currency_indicator_new from a join b on a.Currency_old = b.Currency_old and a.Currency_indicator_old = b.Currency_indicator_old ) set Currency_old = Currency_new, Currency_indicator_old=Currency_indicator_new; ERROR at line 7: ORA-01779: cannot modify a column which maps to a non key-preserved table
attempt – 3 adding PK and OK:
alter table a add constraint a_pk primary key (currency_old, currency_indicator_old); alter table b add constraint b_pk primary key (currency_old, currency_indicator_old); update ( select b.*, a.Currency_new, Currency_indicator_new from a join b on a.Currency_old = b.Currency_old and a.Currency_indicator_old = b.Currency_indicator_old ) set Currency_old = Currency_new, Currency_indicator_old=Currency_indicator_new;
A whole test screenshot
SQL>@test SQL>drop table a; Table dropped. SQL>drop table b; Table dropped. SQL> SQL>create table a ( 2 currency_old varchar(20), 3 currency_indicator_old varchar(20), 4 currency_new varchar(20), 5 currency_indicator_new varchar(20) 6 ); Table created. SQL> SQL>create table b ( 2 currency_old varchar(20), 3 currency_indicator_old varchar(20) 4 ); Table created. SQL> SQL>insert into a values ('GBR','GBRO','LGBR','LGBRO'); 1 row created. SQL>insert into a values ('GB1','GBR1','LGB1','LGBR1'); 1 row created. SQL> SQL>insert into b values ('GBR','GBRO'); 1 row created. SQL> SQL>commit; Commit complete. SQL> SQL>alter table a add constraint a_pk primary key (currency_old, currency_indicator_old); Table altered. SQL>alter table b add constraint b_pk primary key (currency_old, currency_indicator_old); Table altered. SQL> SQL> SQL>update 2 ( 3 select b.*, a.Currency_new, Currency_indicator_new 4 from a join b 5 on a.Currency_old = b.Currency_old and a.Currency_indicator_old = b.Currency_indicator_old 6 ) 7 set Currency_old = Currency_new, 8 Currency_indicator_old=Currency_indicator_new; 1 row updated. SQL> SQL> SQL>commit; Commit complete. SQL> SQL>select * from b; CURRENCY_OLD CURRENCY_INDICATOR_O -------------------- -------------------- LGBR LGBRO 1 row selected. SQL>