I prefer to one connection rather than WAL if it can accomplish the job. But I found that, INSERT can be done in one connection, but UPDATE can not.
Because in my UPDATE case, the new values that will be updated come from another different table, and the UPDATE's WHERE condition also involves columns from more than one tables(actually 3), UPDATE statement doesn't support JOIN, so I didn't find a way to do this in one connection(one query). Currently, the only way I can work out is that, save all SELECT result in a heavy c++ array ( include values that WHERE needs ), then use the same connection to do the UPDATE, but this should not be a smart way. Could anyone point out me another method? PS: INSERT also faces the across-table problem but INSERT...SELECT... can deal with that. Thx On Wed, Mar 14, 2012 at 11:07 AM, Igor Tandetnik <itandet...@mvps.org>wrote: > On 3/13/2012 10:38 PM, FengTao DING wrote: > >> Hi all, >> >> I wrote a piece of code, that open two sqlite3 connections, use >> connection1 >> to do a SELECT query, while stepping in this query, >> >> when some condition matches, it uses connection2 to do a INSERT query; >> when another condition matches, it uses connection2 to do a UPDATE query. >> >> As common result, SQLITE_BUSY or SQLITE_LOCKED(when using >> SQLITE_OPEN_SHAREDCACHE) will return when do INSERT or UPDATE. >> > > Just run your INSERT and UPDATE statements on connection1, don't bother > with the second connection. I assume you are not planning to modify the > same data you are iterating over. > > Another possibility is opening your database in WAL mode: > http://sqlite.org/wal.html . In this mode, readers don't block writers. > The writer will be able to modify the same data the reader is reading (but > the reader won't see those changes). > -- > Igor Tandetnik > > ______________________________**_________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users> > -- Regards, Fengtao _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
