RE: mass update
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <raja4list@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 16 Feb 2005 09:33:16 +0100
this is a typical situation where an updatable join view would be the best
solution.
no scripting, no PL/SQL, just a single SQL statement.
you you get something like:
update (select ... from HS1,HS2 where HS1.SNO = HS2.SNO)
set COL1 = ...
, COL2 = ...
, COL3 = ...
this way you'll do all updates in a single pass.
kind regards,
Lex.
---------------------------------------------
Visit my website at http://www.naturaljoin.nl
---------------------------------------------
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of raja rao
Sent: Wednesday, February 16, 2005 04:09
To: oracle-l@xxxxxxxxxxxxx
Subject: mass update
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'
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
