RE: mass update

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_at_yahoo.com] 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_at_naturaljoin.nl> 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On
	Behalf Of raja rao
	Sent: Wednesday, February 16, 2005 04:09
	To: oracle-l_at_freelists.org
	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'
	
	--
	http://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_at_naturaljoin.nl
	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>

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