I need to update my current working table from source database table.
Update HardwareList tgt
SET Remark = (SELECT Remark FROM TmpHW WHERE tgt.ID = TmpHW.ID and InsertUpdate = 'U')
,Status = (SELECT Status FROM TmpHW WHERE tgt.ID= TmpHW.ID and InsertUpdate = 'U')
,DecomDate = (SELECT DecomDate FROM TmpHW WHERE tgt.ID = TmpHW.ID and InsertUpdate = 'U')
,DecomReason = (SELECT DecomReason FROM TmpHW WHERE tgt.ID = TmpHW.ID and InsertUpdate = 'U')
I need to have 1 select statement for each field that I need to update, if i got 30 fields need to update means I need 30 select statement and I worry this will cause the performance issue cause too many extraction.
Is there any best method to do update?
Other database system like MSSQL, MySQL, PostgreSQL, the update statement can be very simple
Update HardwareList tgt
SET Remark = Remark
FROM TmpHW WHERE tgt.ID = TmpHW.ID and InsertUpdate = 'U'
I really having issue with the performance for the update, and I really hope to see if there are any way to improve it.
1 ACCEPTED SOLUTIONBelow some fully functional sample code that hopefully will show you the way.
/* options to write to the SAS log more info how SAS interacts with the DB */ options sastrace='. d' sastraceloc=saslog nostsuffix; /* define common libname options */ %let pg_conn_common= DATABASE= SERVER="" PORT= authdomain= DBMAX_TEXT=32000 DIRECT_EXE=DELETE DBCLIENT_MAX_BYTES=1 ; /* libname for permanent pg table to be updated */ /* - GLOBAL connection required to use pg temporar tables */ LIBNAME pg_perm POSTGRES CONNECTION=GLOBAL SCHEMA="" &pg_conn_common ; /* libname for temporary pg table with updates */ /* - pg temporar table don't have a schema */ /* - pg temporary tables only exist within the scope of a session */ /* and are only accessible within the scope of a session */ /* (similar to SAS Work tables) */ LIBNAME pg_temp POSTGRES CONNECTION=GLOBAL DBMSTEMP=YES &pg_conn_common ; /* macro to drop tables if they exist */ %macro drop_tbl_if_exists(lref_tbl); %if %sysfunc(exist(&lref_tbl)) %then %do; proc sql; drop table &lref_tbl; quit; %end; %mend; /* drop tables to make sample code re-runnable */ /* - NB: PG temporary tables only exists during a session */ %drop_tbl_if_exists(pg_perm.class); %drop_tbl_if_exists(pg_temp.class_updt); /* load SAS master table into pg permanent table */ /* - SAS will create the pg table if it doesn't exist */ /* inspect SAS log more detail */ proc append base=pg_perm.class data=sashelp.class; run;quit; /* create table for updates in SAS Work */ data work.class_updt; set sashelp.class; if name='Alice' then do; age=99; output; end; if name in ('Alfred','Jane') then do; weight=-10; output; end; run; /* load SAS update table into pg temp table */ /* - SAS will create the pg table if it doesn't exist */ /* inspect SAS log more detail */ proc append base=pg_temp.class_updt data=work.class_updt; run;quit; /* print pg temp table */ proc sql; select * from pg_temp.class_updt order by name; quit; /* update master table with temp table */ /* - using explicit SQL pass-through */ /* - executes fully in-database; SAS just sends the code to the DB */ /* - SQL within the execute() block must be in Postgres syntax */ proc sql; connect using pg_perm; execute by pg_perm ( update prepsegdr.class m set age=t.age, weight=t.weight from class_updt t where m.name=t.name; ); disconnect from pg_perm; quit; /* print pg master table after update */ proc sql; select * from pg_perm.class order by name; quit; The "common" libname connection options might need to be different in your environment (like for dbclient_max_bytes). You find that documented here.
Easiest is normally to use an already existing and working libname as your starting point.
The bits that are not in the common section are MUST and will be the same for you.
I'm showing an example for an UPDATE but of course once you've loaded all your data into Postgres tables it's just Postgres SQL you need for any other operation.