Re: update view

From: Michael Bialik <michael_bialik_at_my-deja.com>
Date: Sun, 17 Dec 2000 21:07:39 GMT
Message-ID: <91j9uo$9t6$1@nnrp1.deja.com>

Hi.

If you are using Oracle 8 (i)- use "INSTEAD OF TRIGGER" on that view.

HTH. Michael.

In article <3A39DAEB.9E4A54D6_at_aaanet.ru>, Maxim Ovchinnikov <eeyore_at_aaanet.ru> wrote:
>
> --------------CCC806570BB8E690155B11C5
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> 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.
>
> --------------CCC806570BB8E690155B11C5
> Content-Type: text/html; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> <!doctype html public "-//w3c//dtd html 4.0 transitional//en">
> <html>
> Hi!
> Could you advise me the best way to update
> more than one base table in a complicated  view?<font
face="Arial,Helvetica">
> 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:<font
face="Arial,Helvetica">
> 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.<font
face="Arial,Helvetica">
> 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?<font
face="Arial,Helvetica">
> Thanks.</html>
>
> --------------CCC806570BB8E690155B11C5--
>
>

Sent via Deja.com
http://www.deja.com/ Received on Sun Dec 17 2000 - 15:07:39 CST