Hi Clemens, thanks for that. Unfortunately is comlains after "SET diff_used," and I get "near ",": syntax error: "
If I seperate it out into stand-alone statemeonts like this: UPDATE tmp_dspace_import SET diff_used = (SELECT tmp_dspace_import.used_mb - ifnull(prev.used_mb, 0) FROM tmp_dspace_import AS prev WHERE prev.date_time < tmp_dspace_import.date_time ORDER BY date_time DESC LIMIT 1), diff_free = (SELECT tmp_dspace_import.free_mb - ifnull(prev.free_mb, 0) FROM tmp_dspace_import AS prev WHERE prev.date_time < tmp_dspace_import.date_time ORDER BY date_time DESC LIMIT 1), diff_free_pc = (SELECT tmp_dspace_import.free_pc - ifnull(prev.free_pc, 0) FROM tmp_dspace_import AS prev WHERE prev.date_time < tmp_dspace_import.date_time ORDER BY date_time DESC LIMIT 1); then it comes back "no error:" but doesn't update anything. Not sure if the abokve is ok - looks ok to me. The only question in my mind is that it won't diferentiate between servers and drives.SOme only have a C drive but most hace a D and E, some have an F. All local drives btw, not mapped. On Tue, Aug 22, 2017 at 10:32 AM, Clemens Ladisch <clem...@ladisch.de> wrote: > Matthew Halliday wrote: > > I used the SQLite Studio to create the table so used the DATETIME data > type > > for that, and although I used yyyy-mm-dd hh:mm:ss in the script > > That is correct. > > > it seems to have reverted it to dd/mm/yy hh:mm:ss. > > That would not be usable. > Check the actual format with the sqlite3 command-line shell. > > > Assuming the timestamp format is usable, you can compute the differences > with a statement like this: > > -- add three columns; then: > UPDATE MyTable > SET (diff_used, diff_free, diff_free_pc) > = (SELECT MyTable.used_mb - ifnull(prev.used_mb, 0), > MyTable.free_mb - ifnull(prev.free_mb, 0), > MyTable.free_pc - ifnull(prev.free_pc, 0) > FROM MyTable AS prev > WHERE prev.date_time < MyTable.date_time > ORDER BY date_time DESC > LIMIT 1); > > This is much faster if there is an index on date_time. > > > Regards, > Clemens > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users