Re: update from multiple rows

Thanks for your answer
In fact the computation is somewhat more complex than an average and the 
data set is quite large... I did some test with view & triggers but it's 
too slow..
Moreover, sometime i need to do big insertion or update and then other time 
i need juste little update of this table...
I would like to apply a trigger only for little update but i don't know how 
to proceed.. Maybe with a condition into the trigger.. But it's adding 
computation time...

Thanks again,
Etienne Adam



> >  I have some trouble updating a table like this one :
> >  date     |     data_raw     |     data_sys
> >  12-01   |   5                   |       4.5
> >  13-01   |   6                   |       6
> >  14-01   |   7                   |       8
> >
> > I would like to update the 'data_sys' row by computing values of 
multiple
> > 'data_raw' values. I mean for example :
> > data_sys(13-01) = (data_raw['12-01'] + data_raw['13-01'] +
> > data_raw['14-01'] )/3;
>
>Is there a reason to maintain data_sys in the table?  Could you use
>a view instead?  A view could do self joins (join the table against
>itself) and perform the necessary calculations on the fly when you
>make a query.  That could be expensive if you select large data
>sets, but for small slices of data it might suffice.
>
>Another possibility might be to use a trigger to recalculate data_sys
>when records are inserted, updated, or deleted.  You'd still have
>to do a potentially expensive one-time update of the entire table,
>but future updates would then touch only the rows that depend on
>the data being inserted, updated, or deleted, and the calculated
>values would always be current.  Using a trigger would require some
>care, however, to avoid cascading updates that are unnecessary or
>that could result in infinite recursion.

_________________________________________________________________
無料メールならやっぱり 「MSN Hotmail」 http://www.hotmail.com/