(Replying to my own email so I can find this answer again via google in the future...)
I have an even better solution... CREATE VIEW NewView AS SELECT * FROM table1 LEFT JOIN table2 on table1.field=table2.field WHERE table2.field is NULL; Works just fine... Cheers Rachel Back In Oct, I asked: Rachel Willmer <[EMAIL PROTECTED]> wrote: > 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? On 14/10/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > 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]>
