You can't replace multiple rows in a single insert/update/delete statement. You might consider copying the duplicates to a temp table, delete them from the old then use a select on the temp table to generate the new rows for the old table. The select portion would be something like select longitude, distance, SUM(IVSUM)/SUM(IVCount), SUM(IVSUM), SUM(IVCount) FROM <temp> GROUP By longitude, Distance.
On 8/16/2011 9:05 AM, Anantha Prasad wrote: > Wanted to know if Sqlite can do simple math when there is a break in > sequence in the data. For example the foll. table is sorted by Longitude and > then Distance: > > Id Longitude Distance IVmean IVsum IVcount > 42 71.0 10 10.5000 2 21 > 43 71.0 10 29.4286 28 824 > 44 71.0 20 9.4677 62 587 > 45 71.0 20 11.6667 144 1680 > 46 71.0 30 3.5532 47 167 > 47 71.0 40 4.5732 49 67 > > I want to compute replace the IVmean for each repeated Distance by > IVsum/IVcount - for repeated Distance - for example, > > Record 42 and 44 should be replaced by > 71.0 10 (2+28)/(21+824) > Record 44 and 45 should be replaced by > 71.0 20 (62+144)/(587+1680) > > Is this possible to do this in Sqlite. > > Thanks much. > Pras > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users