UPSERT / INSERT ON CONFLICT PK Autoincrement
When conflict occurs, no import performs ( as expected ),
but PK is incremented nevertheless.
example (focus on sqlite_sequence.seq) :
run1: initial values - all ok
run2: all conflict; no insert (ok) - but sqlite_sequence.seq incremented ( I
would expect seq unchanged - thus seq=3)
run3: 1x insert + 2x conflict - sqlite_sequence.seq incremented regardless of
number of inserts / conflicts
( I would expect seq incremented depending on number of inserts - thus seq=4)
CREATE TABLE [t1](
[id] INTEGER PRIMARY KEY AUTOINCREMENT,
[col1] TEXT,
[col2] INTEGER DEFAULT 0,
UNIQUE([col1]));
------------------------------------------------
run 1:
insert into t1 (col1) values ('A'),('B'),('C')
on conflict (col1) do update set col2=col2+1 ;
t1:
id col1 col2
1 A 0
2 B 0
3 C 0
sqlite_sequence:
name seq
t1 3
------------------------------------------------
run 2:
insert into t1 (col1) values ('A'),('B'),('C')
on conflict (col1) do update set col2=col2+1 ;
t1:
id col1 col2
1 A 1
2 B 1
3 C 1
sqlite_sequence:
name seq
t1 6
------------------------------------------------
run 3:
insert into t1 (col1) values ('A'),('X'),('C')
on conflict (col1) do update set col2=col2+1 ;
t1:
id col1 col2
1 A 2
2 B 1
3 C 2
8 X 0
sqlite_sequence:
name seq
t1 9
Please, can you look at it ?
thx
StanoZ
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
