On Thu, 21 Feb 2013 10:32:51 -0800 (PST)
Peter Aronson <[email protected]> wrote:
> an outer select loop was reading records, and an inner select loop
> was inserting records based on the records found into the same
> table.
For every row, this design requires 2 trips to the database. Besides
being inefficient, I suspect it's unnecessary. The problem you
encountered goes away if you use your application to construct sets of
information with which to update the database.
If, as you say, the inserted row is "based on the the records found", is
it not possible to issue just one insert statement instead, something
like
insert into new_rows
select ... from old_rows
where key between x and y;
If not -- if the application is adding information -- then could you
insert *only* that information (into a different table) and then
begin transaction;
insert into new_rows
select ... from old_rows
natural join other_table
where key between x and y;
delete other_table;
commit;
For N rows, that would save as much as 2N-1 database calls *and* solve
the "new rows" surprise.
--jkl
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
