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>