RE: better sql code

Raj,

 

How about this:

 

Declare

 

Cursor c1 is

  Select * from adstage;

 

Begin

 

For c1_rec in c1 loop

 

  Update hsp

   Set mr# = c1_rec.mr#,

         Facilty_code = c1_rec.facility_code,

        Etc...etc...

     Where acct# = c1_rec.acct#

      And.....

End loop;

 

Commit;

 

End;

 

Basically, create a cursor and a loop selecting records as needed.
Basic PL/SQL programming.

 

Good Luck!

 

Tom

 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of raja rao
Sent: Tuesday, October 25, 2005 1:16 PM
To: oracle list
Subject: better sql code

 

Hi All,

 

can someone give me a better code for the below:


We use the sqlloder to load somedata into oracle.
that will be loaded into a stage table called adtstage.
Later this should be updated into the main table HSP.


For this purpose, we have develped the code like this.

we will collect the acct# numbers into a flat file (filename is list)
and
for each line will fire theupdate statement like below.

 

cat list|while read line
do
sqlplus -s <<EOF
${connect_string}
@update.sql ${line}
EOF
done 

# list is a file which contains the acct# to be updated into main table.

update.sql contains teh below code:


define hsval=&1
update HSP set
mr#= ( select   mr#     from    adtstage        where   acct#=  &hsval
),
facility_code=( select  facility_code   from    adtstage        where
acct#=  &hsval  ),
Patient_LName= ( select  Patient_LName    from    adtstage        where
acct#=  &hsval  ),

.....

adt_flag='Y'
where acct#= '&hsval' and hsp_id='XXX'
;
commit;


In teh abvoe both tables, acct#, mr# columns are primary key.

There are around 100 cols to be updated from stage to main tables. If
there are a 1000 records it is taking 
long time to update the main table. Becoz, it should run thru the whole
1000 recods and run the above update
which is taking lot of time.

can someone help me in tuning this.


Thanks,
Raj

 

NOTE: Both the tables HSP and ADTSTAGE  are same structures and they
have

acct#, mr# as the primary key.

 

________________________________

Yahoo! FareChase - Search multiple travel sites in one click.
<http://us.lrd.yahoo.com/_ylc=X3oDMTFqODRtdXQ4BF9TAzMyOTc1MDIEX3MDOTY2OD
gxNjkEcG9zAzEEc2VjA21haWwtZm9vdGVyBHNsawNmYw--/SIG=110oav78o/**http%3a/f
arechase.yahoo.com/>

Other related posts: