René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Update of an inline view in Oracle

set feedback off

create table base_table (
  a number,
  b varchar2(10) constraint pk_ldkj primary key
);


insert into base_table values (1,'one'  );
insert into base_table values (2,'two'  );
insert into base_table values (3,'three');
insert into base_table values (4,'four' );


create table ref_table (
  c number,
  d varchar2(10) constraint fk_lll references base_table
);


insert into ref_table values (9,'one'  );
insert into ref_table values (8,'two'  );
insert into ref_table values (7,'three');
insert into ref_table values (6,'four' );
insert into ref_table values (5,'one'  );
insert into ref_table values (4,'two'  );
insert into ref_table values (3,'three');
insert into ref_table values (2,'four' );


update (select a,b,c,d from base_table, ref_table where b=d) set c=a*c;

select * from ref_table;
The select statement returns:
         C D
---------- ----------
         9 one
        16 two
        21 three
        24 four
         5 one
         8 two
         9 three
         8 four
Cleaning up:
drop table ref_table;
drop table base_table;