Thanks everyone. I was mis-equating REPLACE with UPDATE. I’ll change my code to “INSERT or IGNORE” and add an UPDATE.
Mike > On Sep 18, 2016, at 05:34, Keith Medcalf <kmedc...@dessus.com> wrote: > > > On Saturday, 17 September, 2016 21:35, mikeeggl asked: > >> In my DDL I have “integer primary key not null” on my tables. When I do >> the first insert to these tables the rowid is assigned. The insert >> statement is a “insert or replace into…” so I don’t have to deal with >> insertion errors. However, I expected the rowid to not change when the row >> has not changed (when the replace is part of the clause is used). Is there >> a way to not have the rowid changed when “insert or replace into…” is >> used? > > Specify the integer primary key in the insert or replace? > > "INSERT OR REPLACE" > > INSERTs a row if it does not cause an integrity violation. > if the attempt to INSERT the row causes an integrity violation, then REPLACE > deletes (all) the conflicting row(s) then does the INSERT. > > If no integer primary key value is specified (ie, is null) when the INSERT > (either the initial INSERT, or the one after the conflicting rows have been > removed), then a new integer primary key is generated. > > If you do not want the OR REPLACE then perhaps OR IGNORE is what you want. > Possibly preceded by one or more conditional UPDATE(s) to update any > pre-existing rows. > > INTEGER PRIMARY KEY is always not null since the ROWID cannot be null. Your > specification of not null is redundant. The specification of NULL or NOT > NULL on integer primary key columns (even for WITHOUT ROWID tables) is > silently ignored. > > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
