Adding "primary key" to column "a" results in the behavior I think you were first expecting.
sqlite> create table tmp (a integer primary key, b integer); sqlite> create unique index tmpIndex on tmp (a, b); sqlite> insert into tmp values (1, 1); sqlite> insert into tmp values (2, 2); sqlite> select last_insert_rowid(); 2 sqlite> insert or replace into tmp values (1, 1); sqlite> select last_insert_rowid(); 1 sqlite> select * from tmp; 1|1 2|2 I wonder if Michael could use OR REPLACE instead of OR IGNORE to solve his problem. Shawn -----Original Message----- From: Simon Davies [mailto:[EMAIL PROTECTED] Sent: Monday, October 29, 2007 11:02 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() Thanks for the explanation! On 29/10/2007, Dennis Cote <[EMAIL PROTECTED]> wrote: > Simon Davies wrote: > > Following this thread, I was experimenting with last_insert_rowid(), > > and found the following, which does not look right: > > > > SQLite version 3.4.2 > > Enter ".help" for instructions > > sqlite> > > sqlite> create table tmp( a integer, b integer ); > > sqlite> create unique index tmpIndex on tmp( a, b ); > > sqlite> insert into tmp values( 1, 1 ); > > sqlite> insert into tmp values( 2, 2 ); > > sqlite> select last_insert_rowid(); > > 2 > > sqlite> > > sqlite> insert or replace into tmp values( 1, 1 ); > > sqlite> select last_insert_rowid(); > > 3 > > <------ !!!???!!! > > sqlite> select * from tmp; > > 2|2 > > 1|1 > > sqlite> > > > > > > > > > Simon, > > If you change your query to; > > select rowid, * from tmp; > > it will display the rowid which is different than either of the fields > in the table. > > When doing a replace sqlite deletes the existing row and adds a new row. > > HTH > Dennis Cote > > ------------------------------------------------------------------------ ----- > To unsubscribe, send email to [EMAIL PROTECTED] > ------------------------------------------------------------------------ ----- > > ------------------------------------------------------------------------ ----- To unsubscribe, send email to [EMAIL PROTECTED] ------------------------------------------------------------------------ ----- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
