Please , I will be grateful for your help. I ran this update statement using merge but I get ORA-38104: Column referenced in the ON Clause cannot be update. I understand I cannot update the column referenced on the on clause but it is the key to the table. The idea is to update table a using the original code in table b. But the problem is that the code itself need to be updated. This is the code; Thanks in advance.
DECLARE
i NUMBER;
BEGIN
MERGE INTO table_a a
USING table_b t
ON (t.orig_code = a.code)
WHEN MATCHED THEN
UPDATE SET
a.name = t.new_name,
a.code = t.new_code
WHERE a.loc = 24;
i := SQL%rowcount;
dbms_output.Put_line(i||' '||'records updated');
END;
TABLE A
ORIG_NAME ORIG_CODE NEW_NAME NEW_CODE
CONTRACT SALES RAC 7027201381427 CONTRACT SALES RAC (OT) 7027201640287
RAC NEW BUILD SALES 7027201291793 RAC NEW BUILD SALES (OT) 7027201640423
CENTRAL SALES OFFICE RAC 7027201799673 CENTRAL SALES RAC (OT) 7027201640669
SEVENOAKS RAC 7027201017133 SEVENOAKS OT 7027201929627
LOUGHBOROUGH RAC 7027201018741 LOUGHBOROUGH OT 7027201912287
BRIDGWATER RAC 7027201029907 BRIDGWATER OT 7027201902873
BATTERSEA RAC 7027201220777 BATTERSEA RAC 7027201906901
SUTTON RAC 7027201243677 SUTTON RAC 7027201914267
SWANSEA RAC 7027201244713 SWANSEA RAC 7027201684717
TABLE B
LOC NAME CODE
24 ASSOCIATE LTD 7027201381427
24 TRADE COMPANY 7027201291793
24 CORP., LTD. 7027201799673
24 TRADING CO. LTD 7027201017133
24 MELLENIUM ARTS 7027201018741
24 ABELA TRADE LTD 7027201029907
24 JOHNSOND & SONS 7027201220777