0

i am trying to update over 500 millions records with forall loop inside proc sql of SAS, I want to take the value of NG_OFRRELPRD1 from CMBATCH.HashedCNumbers and put it into CDM.EVN_OFFER_ARCH using the UNIQUE_ID field in both tables. here is the code that im using:

proc sql noprint;

    connect using CDM as oracle ;
    execute(
DECLARE 
    CURSOR cur_UNIQ IS SELECT UNIQUE_ID FROM CMBATCH.HashedCNumbers; 
    TYPE tab_KEY IS TABLE OF cur_UNIQ%ROWTYPE INDEX BY PLS_INTEGER; 
    array_unique_id_KEY tab_KEY; 

BEGIN 
    dbms_output.put_line(CHR(9)||'Zaczynam kopiowanie danych: '||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));

    OPEN cur_UNIQ; 
    LOOP FETCH cur_UNIQ BULK COLLECT INTO array_unique_id_KEY LIMIT 10000; 
    EXIT WHEN array_unique_id_KEY.count=0; 

    FORALL indx IN 1 .. array_unique_id_KEY.COUNT SAVE EXCEPTIONS
        UPDATE CDM.MDM_EVN_OFFER_ARCH SET NG_OFRRELPRD1 = (SELECT NG_OFRRELPRD1 FROM CMBATCH.HashedCNumbers WHERE  
        CMBATCH.HashedCNumbers.UNIQUE_ID=array_unique_id_KEY(indx).UNIQUE_ID); 
        COMMIT WRITE batch WAIT; 
    END LOOP; 


    CLOSE cur_UNIQ; 
    dbms_output.put_line(CHR(9)||'Dane skopiowane: '||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')); 
END;
by oracle;  

    disconnect from oracle;
QUIT;

The sas oracle connection is correct as i am able to mess with DB. The problem is that this loop does not respect my WHERE clause and does not join by the UNIQUE_ID from the other table. It takes the first value of NG_OFRRELPRD and updates every single row in the target table in the first iteration.

What could be wrong here?

Mari
  • 143
  • 10

1 Answers1

3

I think you want to check the value you are updating to; I suggest it's NOT the first value of NG_OFRRELPRD and updates every single row but the last. It is NOT ignoring your where clause, but you have it in the wrong place. If you format your query you will see that the clause in only on the subselect not on the update. Therefore, without a where clause every row gets updated with the value selected for every row selected in the collection. 

UPDATE CDM.MDM_EVN_OFFER_ARCH 
   SET NG_OFRRELPRD1 = (SELECT NG_OFRRELPRD1 
                          FROM CMBATCH.HashedCNumbers 
                         WHERE  CMBATCH.HashedCNumbers.UNIQUE_ID=array_unique_id_KEY(indx).UNIQUE_ID);

Perhaps you meant something like:

update cdm.mdm_evn_offer_arch offer
   set ng_ofrrelprd1 = (select ng_ofrrelprd1 
                          from cmbatch.hashedcnumbers hnum 
                         where huum.unique_id=array_unique_id_key(indx).unique_id) 
where offer.unique_id = array_unique_id_key(indx).unique_id;
Belayer
  • 13,578
  • 2
  • 11
  • 22