SQL & PL/SQL

Hi All,

I have a situation where we need to update records in one table based on another:-

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

create table src_acct (acct_no varchar2(50), flex_acct_no varchar2(50), branch_id varchar2(3));
insert into src_acct values ('A1',NULL,'BR1');
insert into src_acct values ('A2',NULL,'BR2');
insert into src_acct values ('A22',NULL,'BR2');
insert into src_acct values ('AA22','FL1','BR2');
insert into src_acct values ('A3',NULL,'BR3');
select * from src_acct;
create table staging_acct (acct_no varchar2(50), branch_id varchar2(3), modified_date date, modified_by varchar2(50));
--delete from staging_acct;
insert into staging_acct(acct_no,branch_id) Values('A1','BR1');
insert into staging_acct(acct_no,branch_id) Values('FL1','BR2');
insert into staging_acct(acct_no,branch_id) Values('A22','BR2');
select * from staging_acct;

We need to update Staging acct table with records from src_acct based on branch, firstly there are direct acct_no which are matching like A1 and A22, but sometimes we get flex_acct_no which matches with main account in Src_acct,

Match staging acct_no with src_acct based on branch, if matches then nothing required, else try matching flex_acct based on branch and if match found then update acct_no from src_acct and modified_date as sysdate, modified_by as user..

i did old school approach of looping through cursor for update then matching record first based on acct_no then flex_acct_no..:-

We will do this for multiple branches so had to introduce one more loop on top of it

DECLARE
    is_acct_exists NUMBER := 0;
    v_acct_no      VARCHAR2(50);
   CURSOR C1_VG_RAW IS 
    SELECT  acct_no, 
                   branch_id
       FROM staging_acct 
        WHERE branch_id = 'BR2'
    FOR UPDATE of acct_no;
BEGIN
        FOR r1_vg in C1_VG_RAW 
                                     LOOP
                                      --check if related account exist for this record in account table for same branch
                                              SELECT count(1)
                                                 INTO is_acct_exists
                                                FROM src_acct a
                                                  WHERE 
                                                  a.branch_id = 'BR2'
                                                  AND a.acct_no = r1_vg.acct_no;
                                             IF(is_acct_exists = 0) THEN --we dint get a match for acct_no, lets check the flexaltacno
                                              
                                               BEGIN
                                                          SELECT a.acct_no
                                                             INTO v_acct_no
                                                            FROM src_acct a
                                                              WHERE 
                                                              a.branch_id = 'BR2'
                                                              AND a.flex_acct_no = r1_vg.acct_no;
                                                               
                                                              --If we found matching flexaltacno so need to update raw data
                                                       UPDATE staging_acct
                                                           set modified_by = user,
                                                             modified_date = sysdate,
                                                             acct_no = v_acct_no
                                                       WHERE CURRENT OF C1_VG_RAW;
                                              
                                                  EXCEPTION
                                                   WHEN OTHERS
                                                   --We didnt get any data so it is not present
                                                   THEN 
                                                       NULL;
                                                  END;
    end if;
    end loop;
END;

Just checking can this be done in SQL with direct update? we might have 300-400K records to update for 4 branches..

Thanks,

Nikhil