First of all in case you've mentioned it will be not nested transaction but one transaction including all deletes and it will be committed when select statement will be finished.
As a second note: it's pretty bad idea to change table which is used in not yet finished select statement. In SQLite it can lead to unpredictable behavior. E.g. rows that you've already deleted can still be selected in subsequent fetches or new rows that you've inserted would be returned by issued earlier select statement. And this can lead effectively to infinite loop. So indeed you better use some memory structures for storing results of your select first, before changing the table. Pavel On Fri, Jul 24, 2009 at 3:12 PM, Guido Ostkamp<o...@ostkamp.fastmail.fm> wrote: > Hello, > > just a short question: > > With the current sqlite version, is it possible to have nested > transactions, e.g. > > exec select ... from table > while (fetch row) { > if (criteria) > exec delete from t where ... > } > > which means execute some 'select', fetching the results in a loop and > inside the loop executing e.g. 'delete' statements on the same table based > on the data returned by the fetch? > > The 'definitive guide to sqlite' book that I have (which is based on an > now outdated version of sqlite) says it isn't and I have to put the > results of the select into another temporary table or local storage to > work around the problem, but I thought I had heard about new support for > nested transactions. > > Thanks for any insight. > > Regards > > Guido > _______________________________________________ > 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