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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.