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/