mass update

From: raja rao <raja4list_at_yahoo.com>
Date: Tue, 15 Feb 2005 19:08:35 -0800 (PST)
Message-ID: <20050216030835.10455.qmail@web61309.mail.yahoo.com>

Hi DBAs,
We have a case here which is taking long time to perform a simple update (becoz, 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
col1
col2
col3
col4

hs2 structure:
sno - pk
col1
col2
col3
col4

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,
Raja

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

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Feb 15 2005 - 22:11:27 CST