Nah, ignore that, I was talking garbage... Using the view is a neater way of doing that select statement, but you still need to create the temp table to avoid the locking issues...
Rachel On 03/12/05, Rachel Willmer <[EMAIL PROTECTED]> wrote: > (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]> >
