Re: [sqlite] rowid changing....
On Sun, Sep 18, 2016 at 4:19 PM, mikeegg1wrote: > Thanks everyone. I was mis-equating REPLACE with UPDATE. I’ll change my > code to “INSERT or IGNORE” and add an UPDATE. > For info, that's a common gotcha with SQLite. See [1] from 2-months ago, but I'm sure the archives are littered with similar examples :). --DD [1] http://sqlite.1065341.n5.nabble.com/insert-or-replace-on-PK-and-UNIQUE-INDEX-td90407.html ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rowid changing....
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 Medcalfwrote: > > > 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
Re: [sqlite] rowid changing....
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
Re: [sqlite] rowid changing....
On 18 Sep 2016, at 7:21am, Clemens Ladischwrote: > mikeegg1 wrote: >> 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. > > The OR REPLACE clause just deletes any old row. > > If you want to update the old row, use UPDATE. (And there's no good way > to do this with a single SQL statement.) Right. If you want the same functionality as INSERT OR REPLACE then do INSERT OR IGNORE ... UPDATE ... If the existing row already exists then the new one won't be inserted, but the UPDATE will affect both new and old rows. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rowid changing....
mikeegg1 wrote: > 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. The OR REPLACE clause just deletes any old row. If you want to update the old row, use UPDATE. (And there's no good way to do this with a single SQL statement.) Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users