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]>
