update view

Hi!

Could you advise me the best way to update more than one base table in a complicated view?

So I have:
create view subquery1 as
select a,b,c,d,e,f
from t1,t2,t3,t4

    where condition1

       and condition2
       and condition3
       and condition4
       and condition5
       and condition6;

Then I need to set a=b,c=d and e=f.But the problem is that a & c reside in one base table(for example t1) and e
resides in another base table (for example t2).As you know Oracle refuses to update more than one base table in one update statement.

I can solve this problem:

Solution1:

update (subquery1)
set a=b,c=d;
update (subquery1)
set e=f;

But I wonder will Oracle calculate subquery1 once(only for the first update statement) or twice(for each update statement)?

I guess second one is right.

So may be the better way to do this will be:

Solution2:

..........
update (subquery1)
set a=b,c=d;

--and instead update (subquery1)

• set e=f;
• we will use:

create view subquery2 as
select e,f
from t2,t3,t4

    where condition1

       and condition2
       and condition3
       and condition4;

update (subquery2)
set e=f;

Hint: subquery2 is simplier(more simply) than subquery1. So if subquery1 will be recalculated two times(in the first solution) then I think it is better to use the second solution,or may be there is another one?

Thanks.

--
Received on Fri Dec 15 2000 - 02:48:43 CST