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/ ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
