SQLite Forum

Nesting of statements
Login
Using a temporary table of Ids is a pattern I've used before in this type of situation.  It works well when the data set isn't too large - it's a good solution, thank you.

Alternatively using a temporary table for the calculated results is a different way I've done similar.

I guess what I was hoping for though were some rules on if UPDATEs during a SELECT are ever deterministic and safe or not.  For example, adding an ORDER BY clause on the select could guarantee the output order with respect to some index, and possibly make things more predictable...

What I'm hearing though is that nesting UPDATES to a table while in a SELECT is unpredictably unsafe - though most of the time sqlite doesn't seem to notice and just skips rows or returns a subset.  I'm not sure if it was me or sqlite, but I may have once got into an infinite loop too... :-/

Anyway, this seems like a bit of a trap in an otherwise impeccable bit of software.  It would be nice if there were either documentation highlighting the weakness inherent in such a use case, or it was consistently trapped as API misuse.

I believe other databases handle such a use case as one may expect, but then they have MVCC or lots of other fancy stuff - the cost of which I don't really want in my embedded applications for which sqlite is perfect!

Thank you all!