On Fri, 2005-05-13 at 15:16 +0100, Brandon, Nicholas wrote: > I think I might be getting confused on this subject as well. Does this mean > that SQLite 3.x can NOT process multiple inserts/updates within one > transaction if it is working on the same table? > > ie Below would return "database table is locked"? > > BEGIN TRANSACTION > SELECT * from table1 WHERE col > x > UPDATE table1 SET col = ... > INSERT INTO table1 .... > COMMIT TRANSACTION >
You cannot simultaneously read and write from the same table. If you are in the middle of a SELECT on a table, you cannot UPDATE or INSERT or DELETE from that table until the SELECT is finished. (NB: the SELECT is usually not finished until you call sqlite3_finalize() or sqlite3_reset() on the statement. For an exception to this rule, see solution (2) below.) You can read and write the same table as many times as you want within the same transaction as long and the reading and writing do not overlap in time. If you want to do an UPDATE on each row of a SELECT on the same table, you can do this in several ways. (1) Load the results of the SELECT into a TEMP table, then loop over the TEMP table to do your UPDATES: CREATE TEMP TABLE temp1 AS SELECT * FROM table1 WHERE...; SELECT * FROM temp1; -- for each row of result do: UPDATE table1 SET ...; (2) Add an ORDER BY clause to the SELECT statement where the ORDER BY clause contains at least one arithmetic expression. For example: SELECT * FROM table1 WHERE ... ORDER BY rowid+1; -- for each row of result do: UPDATE table1 SET ...; (3) Store your UPDATEs in a temp table then execute them after the SELECT has finished: CREATE TEMP TABLE updates(stmt TEXT); SELECT * FROM table1 WHERE ...; -- for each row of result do: INSERT INTO updates VALUES('UPDATE table1 SET ...'); SELECT * FROM updates; -- for each row of result, evaluate the stmt -- column as SQL. You can probably also think of schemes where you store either the SELECT results or the UPDATE statements in memory. Note that technique (2) above works by moving the entire result set into memory for you. Avoid solution (2) if your result set is exceedingly large. -- D. Richard Hipp <[EMAIL PROTECTED]>