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