Updating 2 million table from another 2 mio table

[dincooo]

Hi experts,

I have a table named table_a with 2 million records. I want to update a column of table_a from another 2 millon rows table-  named table_b.

My query is:

proc sql;
update table_a u
set BIRTH_PLACE=(select BIRTH_PLACE from table_b as n
where u.party_number=n.party_number)
where u.party_number in (select party_number from table_b);
quit;

This query takes too much time and never ending. Do you have any suggestion how to update this table?

Many thanks,

Onur

[tomrvincent]

Is table_a a sas dataset?

[dincooo]

These two datasets are Oracle tables and we have the right to see them at SAS EG.

[tomrvincent]

Then I'd suggest doing the update with a macro loop, one record at a time (1st step in the loop would be to pick minimum party number greater than previously selected party number). At least that would not be 'never ending'.

[dincooo]

Hi, 

Thank you for the answer. If it is possible can you please write the code that does like this?

[Shmuel]

Alternatively -

1) Load both tables, ID and the variable to update, into sas;

2) Run update in SAS. If ID relation between tables is 1:1 or 1:N (not many to many)

    then better use data step with merge or update staement. 

   It will be much faster then SQL.

3) Update Oracle table from sas result table.

    You can use obs and firstobs option2 to manage update part by part.

    (step 1: obs=1000; step 2:  firstobs=1001 obs=1000, step 3: obs=2001 obs=1000, etc.)

[dincooo]

Hi,

Thank you for your comment can you please write the code that does the last step?

Many thanks

[Shmuel]

I have no oracle environment, now, to check my code.

Here is a skeleton to adapt to your environment and needs.

Code should run on sas with access to oracle:

/* step 1 - define connection parameters */
LIBNAME DBCON ORACLE USER=&UserNm PASS=&PassWd PATH='EDWP' SCHEMA=IDWE CONNECTION=GLOBAL;

/* step 2 - load tables from oracle, relevant variables only */
PROC SQL;
	connect using DBCON;
        select * from connection to oracle
            (create table1 as
                select party_number, birth_place from table_a);
            (create table2 as
                select party_number, birth_place from table_b);
    disconnect from oracle; 
quit;

/* sort tables in sas, prepared to create updated table3 */

proc sort data=table1; by party_number; run;
proc sort data=table2; by party_number; run;

data table3; 
  merge table1(in=in1 rename=(birth_date=bd))
             table2(in=in2);  
    by party_number;
         /* keep observation only those in both tables and date differ */
         if in1 and in2 and birth_date ne bd;
         drop bd;
run;

/* Check table3 is birth_date updated as desired !!! */
/* check log - is updated observations count logic */

PROC SQL;
	connect using DBCON;
	select *  from connection to oracle   	
            (update table_a as a   from table3 as n
              set a.BIRTH_PLACE=n.birth_date
              where a.party_number = n.party_number
             );

    disconnect from oracle; 
quit;

By keeping only those observations in both tables and with different birth dates,

you eliminate number of observations to update and save time.

NOTE - check table3 before trying update oracle table.

Continue only if you accept the results.