SQLite Forum

facing issue from sqlite3_step()
The problem of modifying the data source as you read it is quite common. There are two solutions:

a) deal with the side effect that altering a record my change its position in the visitation sequence, so that any given record may be retrieved 0 to infinity times

b) separate the read and update operations.

For option b) you can

1) formulate what you are attempting to do as an UPDATE query. This will visit each record exactly once and perform the specified changes, while observing any constraints you have defined


2) within a single transaction on a single connection, read all of the rows, taking note of any changes required, and then, after the last row was read, write back any necessary changes, handling any constraint errors as they occur