It's probably not a good idea, because it depends on some
behaviour that is not specified, but I once used a trick
like this to get good performance:
CREATE TABLE abc(a, b);
UPDATE abc SET b = user2(a, b) WHERE <condition> AND user1(a, b);
SQLite loops through the rows where <condition> is true, and
remembers those for which user1() returns true. It then runs
a second loop through those rows and calls user2() for each
of the remembered rows, setting 'b' to the return value.
But like I said, it depends on unspecified behaviour so you
had better have some assert() statements to make sure SQLite
is calling the functions in the expected order.
Dan.
--- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:
> 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]>
>
>
__________________________________
Yahoo! Mail Mobile
Take Yahoo! Mail with you! Check email on your mobile phone.
http://mobile.yahoo.com/learn/mail
