On Thu, Jan 07, 2010 at 03:49:59PM +0300, Max Vlasov scratched on the wall:
> I have a query "INSERT ... SELECT" and after it performed it I have to to
> store range of rowids (autoincrement) of the inserted rows. While max(rowid)
> for right bound seems ok, assuming max(rowid)+1 for the left bound (before
> the query) depends on the fact whether there were deletes from the table
> before. If there were deletes, my estimation will differ from the actual
> value. So is there a correct way to know the range of rowids in this case or
> just to know for sure "expected" rowid in similar cases?
There isn't a correct way of doing this. You need to manually loop
over the SELECT, do the INSERT, and call sqlite3_last_insert_rowid()
(or the SQL function last_insert_rowid()) after each INSERT to build
up a collection of ROWIDs.
With "INSERT... SELECT" there are several techniques to make a very
good guess, such as setting AUTOINCREMENT and then looking at
pre-INSERT and post-INSERT values, but there are some cases when
this may not work. In specific:
http://www.sqlite.org/autoinc.html
AUTOINCREMENT guarantees that automatically chosen ROWIDs will be
increasing but [does] not [guarantee] that they will be sequential.
(BTW, someone needs to review this section... it looks half-edited.)
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
