Re: update from multiple rows

On Sat, Jan 22, 2005 at 12:51:20PM +0000, adam etienne wrote:
>
> 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.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/