Why do it in the call back? Why not just do it in sql: CREATE TEMP TABLE diffs AS SELECT * FROM table1 LEFT JOIN table2 ....;
insert into table1 SELECT * FROM diffs; -- Insert into table1 DROP TABLE diffs; On 10/14/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > Rachel Willmer <[EMAIL PROTECTED]> wrote: > > Hi, apologies if this is a trivial question, but I'm a newbie to > > sqlite3. (very impressed so far) > > > > I want to search two tables which should contain the same records and > > add any that are missing from the second into the first. > > > > So I do > > > > SELECT * FROM table1 LEFT JOIN table2 on table1.field=table2.field > > WHERE table2.field is NULL > > > > So far, so good, I get the records I want. Then in the callback, I try > > > > INSERT INTO table1 etc... > > > > This fails with a "database table is locked" error. > > > > I'm assuming that this is because I'm still in the middle of doing the > > SELECT statement. > > > > So my question is this, do I have to use the callback to copy the > > records into a temp table, and then only add them after the > > sqlite3_exec() which calls the SELECT has returned? or is there a more > > elegant/obvious solution? > > > > Solution 1 is to use a TEMP table: > > CREATE TEMP TABLE diffs AS > SELECT * FROM table1 LEFT JOIN table2 ....; > SELECT * FROM diffs; -- Insert into table1 in the callback; > DROP TABLE diffs; > > Solution 2 is a dirty trick. It works now and in all historical versions > of SQLite and there are no plans to change it, but there are also no > promises not to change it. In solution 2, add > > ORDER BY +table1.rowid > > to the end of your SELECT statement. The "+" sign in front of the > "table1.rowid" is *essential* if this is trick is to work. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > -- --- The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264
