Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: update from selected data?

Re: update from selected data?

From: Heggelund <d92hegge_at_ix_prod.hfk.mil.no>
Date: 1997/04/15
Message-ID: <1997Apr15.134652.1325@ix_prod.hfk.mil.no>#1/1

Hallvard B Furuseth (h.b.furuseth_at_usit.uio.no) wrote:
: Thanks for all the answers to my last question.
 

: I know I'll turn very red when I get the answer to this one, but I've
: apparently been staring too long at my books. So, how do I do this in
: Oracle?
 

: UPDATE em ; table em
: -->> FROM oldEm ; from table oldEm
: SET em.dept = oldEm.dept
: WHERE em.dept IS NULL
: -->> AND oldEm.id = em.id;
 

: I hope there is something more efficient than
 

: UPDATE em
: SET dept = (SELECT dept FROM oldEm WHERE oldEm.id = em.id)
: WHERE em.dept IS NULL
: AND EXISTS (SELECT * FROM oldEm WHERE oldEm.id = em.id);

: --
: Regards,
 

: Hallvard

--

Yes, this is the syntax and it is efficient. But DO NOT use SELECT * in your
subquery, use SELECT NULL. If oldEm has a lot of columns they all had to be 
retrievd due to the '*' (wich says 'give me all columns'). If you use 'NULL' 
only an uniqe index scan is neccessary (no need to access table since no data
are retrived).

Regards
Steinar Heggelund

----------------------------------------------------------------------------
I'm employed in the Norwegian consulting company Opus One AS. 
I have 7 years experience with Oracle products, mainly the database. 
We are a small company which offers consulting services in design,
implementation and tuning of databases and applications based on Oracle.

My postings represent my opinions and they may be wrong. 
Received on Tue Apr 15 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US