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]>
