Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: update from selected data?
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