mass update

  • From: raja rao <raja4list@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 15 Feb 2005 19:08:35 -0800 (PST)

Hi DBAs,
We have a case here which is taking long time to perform a simple update 
we are using unix shell scirpt instead of using a simple update statement)
we have 2 tables hs1 hs2 (with identical structure)
hs1 structure:
sno    - pk

hs2 structure:
sno    - pk

The data comes to HS1 and HS2 from different sources. HS1 is loaded
with first 2 cols (sno, col1)
Later the data will come into HS2 with all the values (from sno to col4)
Now we want to update the table HS1 with all the values from HS2
after all the rows are updated, we truncate the table HS2 and again few new 
rows comes into HS1
and HS2 (from various sources) again the above process will continue.
I am using the below process to update it:
define hsval=&1
update hs1 set
col1 = ( select col1 from hs2 where sno= &hsval ),
col2 = ( select col2 from hs2 where sno= &hsval ),
col3 = ( select col3 from hs2 where sno= &hsval )
( The &hsval is passed to sql everytime. )

since i am not familiar with sql updates, I am using shell script to capture 
all the
new arrived sno and put them in os file and performing a 
while true loop and in that loop i will connect to sql and performing update.
Can someone suggest me a update staement in SQL which can do better job.
Thanks in advance,


Do you Yahoo!?
 Yahoo! Search presents - Jib Jab's 'Second Term'


Other related posts: