Re: [sqlite] rowid changing....

[Dominique Devienne]

On Sun, Sep 18, 2016 at 4:19 PM, mikeegg1  wrote:

> 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....

[mikeegg1]

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  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

Re: [sqlite] rowid changing....

[Keith Medcalf]

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....

[Simon Slavin]

On 18 Sep 2016, at 7:21am, Clemens Ladisch  wrote:

> 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....

[Clemens Ladisch]

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