We've got a series of large tables in PostgreSQL (over 500 million rows in some). Each table has an attribute called REF_ID, and one called OBJECT_CD. Both are varchar(32). OBJECT_CD is always NULL, but now we need to give it a value (on all rows). Let's call one of these large tables T1.
We've also got the table T2. It's a smaller table that contains 1 row per REF_ID. The attribute OBJECT_CD is always filled out here.
I want to update T1.OBJECT_CD with values from T2.OBJECT_CD (where T1.REF_ID = T2.REF_ID).
Sounds simple, but I have a hard time finding an effective way to do it. The PostgreSQL tables are so large, and the code I've tried runs "forever" and times out.
An example of something I've tried:
proc sql;
update pgsql.T1
set OBJECT_CD = (select distinct OBJECT_CD
from T2
where T1.REF_ID = T2.REF_ID);
quit;
Any advice on the most effective way to update huge PostgreSQL tables?
As others suggested load the small table into Postgres and then execute everything in-database. Code close to below should work.
%let pg_libref=pgsql;
%let pg_schema=;
proc sql noprint;
select sysvalue into :pg_schema trimmed
from dictionary.libnames
where
libname="%upcase(&pg_libref)"
and sysname='Schema/Owner'
;
quit;
proc datasets lib=&pg_libref nolist nowarn;
delete T2;
run;quit;
proc append data=work.t2 base=&pg_libref..t2;
run;quit;
proc sql;
connect using &pg_libref as pg;
execute
(
update &pg_schema..T1
set OBJECT_CD = ( select OBJECT_CD
from &pg_schema..T2
where T1.REF_ID = T2.REF_ID)
;
) by pg;
disconnect from pg;
quit;
proc datasets lib=&pg_libref nolist nowarn;
delete T2;
run;quit;
Is t2 a sas dataset or another postgres table?
T2 is a SAS dataset.
Try loading it to the DB and do the entire update there.
As others suggested load the small table into Postgres and then execute everything in-database. Code close to below should work.
%let pg_libref=pgsql;
%let pg_schema=;
proc sql noprint;
select sysvalue into :pg_schema trimmed
from dictionary.libnames
where
libname="%upcase(&pg_libref)"
and sysname='Schema/Owner'
;
quit;
proc datasets lib=&pg_libref nolist nowarn;
delete T2;
run;quit;
proc append data=work.t2 base=&pg_libref..t2;
run;quit;
proc sql;
connect using &pg_libref as pg;
execute
(
update &pg_schema..T1
set OBJECT_CD = ( select OBJECT_CD
from &pg_schema..T2
where T1.REF_ID = T2.REF_ID)
;
) by pg;
disconnect from pg;
quit;
proc datasets lib=&pg_libref nolist nowarn;
delete T2;
run;quit;
@EinarRoed I've added a few obvious fixes to the post you already accepted as solution. Still totally untested code of course but I've used something similar in a real implementation where things work.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.