I have a table with an additional index and a query: "create table Transactions (Id integer primary key not null, Parent references Transactions(id), Body varchar);"
"create index Parent_Index on Transactions (Parent);" EXPLAIN QUERY PLAN insert or replace into Transactions (Id, Parent, Body) values (?1, ?2, ?3); 23 0 0 SCAN TABLE Transactions 43 0 0 SCAN TABLE Transactions The double SCAN TABLE seems to have something to do with both the "references" column and the "or replace" statement. If I remove either, then the SCAN goes away. Questions: - Is my syntax for the " insert or replace" statement correct, and will it do what I expect (insert a new row with a new auto-generated Id if Id is NULL, insert a new row with the given Id if Id is not NULL and no row exists with that Id, or update the existing row with the given Id if Id is not NULL and a row exists with that Id)? - Is sqlite really doing one or two table scans to perform the "insert or replace" with a "references" self column? - If so, is there a way to eliminate this (other than removing either the "references" or the "or replace")? Thanks much. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
