On Tue, 12 Jul 2016 15:35:20 +0200
Dominique Devienne wrote:
> Now we know OR REPLACE is never what we want (in our use cases),
Besides being nonstandard, REPLACE is not atomic. I haven't seen the
use case that benefits from those characteristics but, judging
from this list, you're not the firs
On 2016/07/12 3:55 PM, R Smith wrote:
On 2016/07/12 2:12 PM, Dominique Devienne wrote:
In the session above, we can see that an insert or replace w/o an id
(the
PK) value,
results in the id changing in the table, which is not what we'd like.
etc.
By the way, another important problem with
On 2016/07/12 2:12 PM, Dominique Devienne wrote:
In the session above, we can see that an insert or replace w/o an id (the
PK) value,
results in the id changing in the table, which is not what we'd like.
If you ask any DB engine to INSERT, and you do not specify the
autoinc/pk/identity ID, t
Thank you all, Clemens, Richard, Simon, Hick.
Now we know OR REPLACE is never what we want (in our use cases),
and will rewrite into two statements, as Clemens and Simon indicated. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
h
sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dominique
Devienne
Gesendet: Dienstag, 12. Juli 2016 14:39
An: SQLite mailing list
Betreff: Re: [sqlite] insert or replace on PK and UNIQUE INDEX
On Tue, Jul 12, 2016 at 2:31 PM, Richard Hipp wrote:
> On 7/12/16, Dominique Devienne
On 12 Jul 2016, at 1:39pm, Dominique Devienne wrote:
> So the plural in "deletes pre-existing rows" explain that in my second
> example,
> with both the PK and NK where each point to different rows, both rows are
> first
> deleted, then the new row is inserted?
Correct. There is a common assum
On Tue, Jul 12, 2016 at 2:31 PM, Richard Hipp wrote:
> On 7/12/16, Dominique Devienne wrote:
> >
> > Is that normal or expected?
>
> The operation of REPLACE is defined here:
>
> https://www.sqlite.org/mark/lang_conflict.html?the+REPLACE+al*+current+row#mark
So the plural in "deletes pre-exist
On 12 Jul 2016, at 1:26pm, Clemens Ladisch wrote:
> An INSERT OR UPDATE (or MERGE) statement does not exist in SQLite.
>
> Just try the UPDATE, and if the number of affected rows is zero, do the
> INSERT.
I prefer to do
INSERT OR IGNORE ...
UPDATE ...
That way you do not have to count the af
On 7/12/16, Dominique Devienne wrote:
>
> Is that normal or expected?
The operation of REPLACE is defined here:
https://www.sqlite.org/mark/lang_conflict.html?the+REPLACE+al*+current+row#mark
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing
Dominique Devienne wrote:
> sqlite> select * from t;
> 1|one bis
> 2|two
> sqlite> insert or replace into t (name) values ('one bis');
> sqlite> select * from t;
> 2|two
> 3|one bis
>
> In the session above, we can see that an insert or replace w/o an id (the PK)
> value,
> results in the id chang
On Tue, Jul 12, 2016 at 2:12 PM, Dominique Devienne
wrote:
> C:\Users\ddevienne>sqlite3
> SQLite version 3.10.2 2016-01-20 15:27:19
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table t (
C:\Users\ddevienne>sqlite3
SQLite version 3.10.2 2016-01-20 15:27:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t (id integer primary key autoincrement, name text
unique);
sqlite> insert i
12 matches
Mail list logo
