Confusion re UPSERT syntax error

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Confusion re UPSERT syntax error

Matt
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Confusion re UPSERT syntax error

David Raymond
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:[hidden email]] On Behalf Of Matt
Sent: Wednesday, January 16, 2019 11:30 AM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Confusion re UPSERT syntax error

Simon Slavin-3
On 16 Jan 2019, at 4:59pm, David Raymond <[hidden email]> wrote:

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

though if you're going to quote your identifiers, it should really be

insert into "person" ("name") values ('hello')...

It's better just not to use double quotes at all, unless you have no control over identifiers and they may have punctuation in.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Confusion re UPSERT syntax error

Matt
In reply to this post by Matt
Thanks so much! I figured I was missing something :)

Matt Parsons
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

sqlite3_interrupt

Michele Pradella
In reply to this post by Matt
Hi all, I was looking to the  sqlite3_interrupt to make my application
closing faster without waiting for long standing DB operation. I read in
the documentation that should not be a problem to call it during insert
update or delete: if transaction is running is automatically rolled
back.

Do you think there's some extra care I have to deal with before using
interrupt function? Do you have some experience in using it during
read/write operation on DB?

regards
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users