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: Thomas kyte <tkyte_at_us.oracle.com>
Date: 1997/04/07
Message-ID: <33484f57.2458044@newshost>#1/1

On 6 Apr 1997 19:16:34 GMT, h.b.furuseth_at_usit.uio.no (Hallvard B Furuseth) 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);
>
>

Well the:

udpate em

   set dept = ( select dept from oldEM where oldem.id = em.id )  where em.dept is null
   and exists ( select NULL from oldem where oldem.id = em.id )

is the only way to do it in versions 7.2 and before....

As for the other syntax being more efficient.... It would have to:

1.) full scan EM (since no constraint on EM other then em.dept is null and Nulls are not indexed so no index can be used on EM)

   2.) index read into OLDEM by ID

And the update that works would have to.....

do the same thing. full scan EM and index read into oldem.

If you really want to do it as an update of a join (although it will do the same thing) in 7.3 you can:

drop table em;
drop table oldem;  

create table em as select * from scott.emp; create unique index em_idx on em(empno);  

create table oldem as select * from scott.emp; create unique index oldem_idx on oldem(empno);  

update em set deptno = NULL;
commit;  

create or replace view em_oldem
as
select em.deptno em_deptno, oldem.deptno oldem_deptno   from em, oldem
 where em.empno = oldem.empno
   and em.deptno is null
/  

update em_oldem set em_deptno = oldem_deptno /

You need the unique index on the join condition....

>--
>Regards,
>
>Hallvard

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities



Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Apr 07 1997 - 00:00:00 CDT

Original text of this message

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