In the docs there is this line:
https://www.sqlite.org/lang_UPSERT.html
"The conflict target is required for DO UPDATE upserts, but is optional for DO
NOTHING. A DO NOTHING upsert without a conflict target works the same as an
INSERT OR IGNORE."
The charts are pretty, and useful, but they can show illegal paths due to
various rules like this.
Also note on quotes, single is for a text literal, double is for identifiers.
So it should be
insert into person ("name") values ('hello')...
or just plain
insert into person (name) values ('hello')...
-----Original Message-----
From: sqlite-users [mailto:[email protected]] On
Behalf Of Matt
Sent: Wednesday, January 16, 2019 11:30 AM
To: [email protected]
Subject: [sqlite] Confusion re UPSERT syntax error
Hello,
I am experimenting with the SQLite Upsert syntax:
https://www.sqlite.org/lang_UPSERT.html
Based on the grammar, I would expect a query like this to work:
create table person (
name text primary key
count int default 0
);
insert into person ('name') values ('hello')
on conflict do update set count = excluded.count + 1;
However, I get a syntax error:
> while attempting to perform prepare "insert into person (name) values
('a') on conflict do update set count = excluded.count + 1;": near
"update": syntax error
It would appear that the index list is required by the implementation, as
this works:
insert into person ('name') values ('hello')
on conflict (name) do update set count = excluded.count + 1;
The index list is not required with "nothing":
insert into person ('name') values ('hello')
on conflict do nothing;
Am I misreading the grammar chart? If this is a difference between the
grammar and the implementation, which one should be updated?
Thanks,
Matt Parsons
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
