We have 2 tables

one having 4 columns and second has 2 columns.

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));

We have requirement that we need to compare data from B with first two columns of table A and update/insert in the table B with values in 3rd and 4th column of table A.

by update/insert i mean we should either update the two columns where match is found and replace them with entries from table A(3rd and 4th column) or else Insert an additional row in table B with 3rd and 4th column from A without replacing the row.

Example:

Table A

Currency_old Currency_indicator_old Currency_New Currency_indicator_new
GBR GBRO LGBR LGBRO


Table B
Currency_old Currency_indicator_old
GBR GBRO

Since Table A and Table B has matching 1st and 2nd column we need to insert and entry in B

Table B
Currency_old Currency_indicator_old
GBR GBRO
LGBR LGBRO

In case if we are updating result should be

Table B
Currency_old Currency_indicator_old
LGBR LGBRO

In case no match, do nothing.

In case Match not found we do not need to do anything.

I have tried using Merge clause for the same also not able to find solution using update clause but not able to achieve the result , can you let me know how can this be achieved.

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 b values ('GBR','GBRO');
commit;

merge into b 
using (select a.* from a join b 
       on a.Currency_old = b.Currency_old 
       and a.Currency_indicator_old = b.Currency_indicator_old) a
on    ( a.Currency_new = b.Currency_old 
and   a.Currency_indicator_new = b.Currency_indicator_old )
when not matched then insert
 values (a.Currency_new, a.Currency_indicator_new); 
 
select * from b;

CURRENCY_OLD  CURRENCY_INDICATOR_OLD  
GBR           GBRO                    
LGBR          LGBRO