Re: Functions in Update

On 13 Feb 2004 22:09:21 -0800, newtophp2000_at_yahoo.com wrote:

>I have a query that uses a couple of custom-developed functions to
>update two columns. The syntax I am using is as follows. Mytable has
>col1, col2 and col3 and all are type number.
>
>UPDATE mytable SET col2 = func1(col1), col3 = func2(col1, col2);
>
>The thing is that when the query is executed, the col3 values are not
>updated as expected.

"Expected"...func2 using the old value of col2 IS the expected behaviour from Oracle. Simply

update mytable

      set col3 = func2(col1,func1(col2)),
           col2 = func1(col1);

or to spell it out completely and if CPU and IO-cost is no issue:

update mytable

     set col2 = func1(col1);

update mytable

      set col3 = func2(col1,col2)

• Kenneth Koenraadt

>That is, func2 uses the old values of col2 and
>not the newly computed values. Is there a way to resolve this?
>
>This is on Oracle 9.2 on Windows XP.
Received on Sun Feb 15 2004 - 06:16:49 CST