RE: mass update
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: "'raja rao'" <raja4list@xxxxxxxxx>
- Date: Wed, 16 Feb 2005 15:49:21 +0100
I don't have your environment, with the tables, so I can't check the statement
myself.
This is probably close to what you need:
update (select hs1.col1 as hs1_col1
, hs1.col2 as hs1_col2
, hs1.col3 as hs1_col3
, hs2.col1 as hs2_col1
, hs2.col2 as hs2_col2
, hs2.col3 as hs2_col3
from hs1, hs2
where hs1.sno = hs2.sno)
set hs1_col1 = hs2_col1
, hs1_col2 = hs2_col2
, hs1_col3 = hs2_col3;
Lex.
________________________________
From: raja rao [mailto:raja4list@xxxxxxxxx]
Sent: Wednesday, February 16, 2005 15:30
To: Lex de Haan
Subject: RE: mass update
Hi Lex,
Really I am poor in sql.
Can you give the complete statement.
i failed to write the statement even with the below hint also ..... :(
Thanks,
Srinivas
Lex de Haan <lex.de.haan@xxxxxxxxxxxxxx> wrote:
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 a! m 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
BEGIN:VCARD
VERSION:2.1
N:de Haan;Lex
FN:Lex de Haan
ORG:Natural Join B.V.
TEL;WORK;VOICE:+31.30.2515022
TEL;HOME;VOICE:+31.30.2518795
TEL;CELL;VOICE:+31.62.2955714
TEL;WORK;FAX:+31.30.2523366
ADR;WORK:;;Pieter Breughelstraat 10;Utrecht;;3583 SK;Netherlands
LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Pieter Breughelstraat
10=0D=0AUtrecht 3583 SK=0D=0ANetherlands
URL;WORK:http://www.naturaljoin.nl
EMAIL;PREF;INTERNET:lex.de.haan@xxxxxxxxxxxxxx
REV:20040224T160439Z
END:VCARD
________________________________
Do you Yahoo!?
Yahoo! Search presents - Jib Jab's 'Second Term'
<http://us.rd.yahoo.com/evt=30648/*http://movies.yahoo.com/movies/feature/jibjab
inaugural.html>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
