Re: [sqlite] how to get the INTEGER PRIMARY KEY for the row just inserted?
Jay wrote: Oh. Nifty. I would therefore be safe doing this: begin immediate; insert into master_table() values(); insert into related_table( master_id, data ) select last_insert_rowid(), 'stuff'; commit; But it would fail if I had multiple related tables? begin immediate; insert into master_table() values(); insert into related_table( master_id, data ) select last_insert_rowid(), 'stuff'; insert into related_table2( master_id, data ) select last_insert_rowid(), 'wrong id inserted here'; commit; The last_insert_rowid() would be the id for the last related table not the master table. Yes, that's how it would work. This function is normally intended to be used from C code using the API function, or in SQL triggers. If you need to use the value to link multiple tables on an insert, you need to save it somewhere safe. You could use a dedicated table with a single row like this. create table last_inserted_master (master_id integer); insert into last_inserted_master values(NULL); insert into master_table values(...); update last_inserted_master set master_id = select last_insert_rowid(); insert into related_table1(master_id, data) values((select master_id from last_inserted_master), ...); insert into related_table2(master_id, other_data) values((select master_id from last_inserted_master), ...); The last_insert_rowid value is not updated by inserts that occur inside a trigger. Well, actually it is, and those values are available inside the trigger, but the initial value (before the trigger executed) is restored when the trigger ends. When a trigger starts it can read the value of the last_insert_rowid from its calling context (usually the mainline code, but triggers can be nested as well). So, this update code could all be moved into a trigger that fires immediately after the insert. This can be useful for audit trail tables etc., where the information to be stored in the related table can be determined from existing tables and the new row. HTH Dennis Cote
Re: [sqlite] how to get the INTEGER PRIMARY KEY for the row just inserted?
> >>begin immediate; insert; select max(id) from blah; commit; > >> > >> > > > >Or "select last_insert_rowid() from blah limit 1" > > > >Regards > > > > > > > Better yet > > select last_insert_rowid(); > > The from clause is not needed and may imply that SQLite keeps the > last > inserted rowid for each table, which it does not. The limit clause > is > also unnecessary since the last_insert_rowid function always returns > a single result. Oh. Nifty. I would therefore be safe doing this: begin immediate; insert into master_table() values(); insert into related_table( master_id, data ) select last_insert_rowid(), 'stuff'; commit; But it would fail if I had multiple related tables? begin immediate; insert into master_table() values(); insert into related_table( master_id, data ) select last_insert_rowid(), 'stuff'; insert into related_table2( master_id, data ) select last_insert_rowid(), 'wrong id inserted here'; commit; The last_insert_rowid() would be the id for the last related table not the master table. __ Yahoo! Messenger Show us what our next emoticon should look like. Join the fun. http://www.advision.webevents.yahoo.com/emoticontest
Re: [sqlite] how to get the INTEGER PRIMARY KEY for the row just inserted?
Kurt Welgehausen wrote: begin immediate; insert; select max(id) from blah; commit; Or "select last_insert_rowid() from blah limit 1" Regards Better yet select last_insert_rowid(); The from clause is not needed and may imply that SQLite keeps the last inserted rowid for each table, which it does not. The limit clause is also unnecessary since the last_insert_rowid function always returns a single result. HTH Dennis Cote
Re: [sqlite] how to get the INTEGER PRIMARY KEY for the row just inserted?
> begin immediate; insert; select max(id) from blah; commit; Or "select last_insert_rowid() from blah limit 1" Regards
Re: [sqlite] how to get the INTEGER PRIMARY KEY for the row just inserted?
The documentation is your friend. long long int sqlite3_last_insert_rowid(sqlite3*); http://sqlite.org/capi3ref.html#sqlite3_last_insert_rowid or begin immediate; insert; select max(id) from blah; commit; --- jack wu <[EMAIL PROTECTED]> wrote: > i have a table which has an ID column defined as > INTEGER PRIMARY KEY. I'd like to know the value of the > automatically generated ID right after the insert. is > there anyway to get the ID without issuing another > select? does the insert function return the ID in any > way? if another select is necessary, what is the best > way to do it? this is kind of a generic problem so i > am hoping whoever solved this issue before could share > some thoughts. Thanks for the help. > > > jack. > - You a Gamer? If you're near Kansas City, ask me about the Recruits and Conquest conventions. - The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264 __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[sqlite] how to get the INTEGER PRIMARY KEY for the row just inserted?
i have a table which has an ID column defined as INTEGER PRIMARY KEY. I'd like to know the value of the automatically generated ID right after the insert. is there anyway to get the ID without issuing another select? does the insert function return the ID in any way? if another select is necessary, what is the best way to do it? this is kind of a generic problem so i am hoping whoever solved this issue before could share some thoughts. Thanks for the help. jack.
