Re: [sqlite] Confusion re UPSERT syntax error
On 16 Jan 2019, at 4:59pm, David Raymond 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Confusion re UPSERT syntax error
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:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Matt
Sent: Wednesday, January 16, 2019 11:30 AM
To: sqlite-users@mailinglists.sqlite.org
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
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
[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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Link errors with SQLITE_OMIT_VIRTUALTABLE
It's important to mention Orgad Shaneh, a dear colleague who helped me get to this solution. Patch contents: From 4184b9fc3cbaeabe1892e5d68ddb886373c8ea00 Mon Sep 17 00:00:00 2001 From: Yehiel Zohar Date: Tue, 15 Jan 2019 09:29:46 +0200 Subject: [PATCH] Fix link errors with SQLITE_OMIT_VIRTUALTABLE sqlite3.c:150722: undefined reference to `sqlite3VtabFinishParse' sqlite3.c:150725: undefined reference to `sqlite3VtabFinishParse' sqlite3.c:150729: undefined reference to `sqlite3VtabBeginParse' sqlite3.c:150733: undefined reference to `sqlite3VtabArgInit' sqlite3.c:150738: undefined reference to `sqlite3VtabArgExtend' --- src/sqliteInt.h | 6 +- 1 file changed, 5 insertions(+), 1 deletion(-) diff --git a/src/sqliteInt.h b/src/sqliteInt.h index fe0bd6226..3f62080af 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -4388,6 +4388,10 @@ void sqlite3AutoLoadExtensions(sqlite3*); # define sqlite3VtabUnlockList(X) # define sqlite3VtabSavepoint(X, Y, Z) SQLITE_OK # define sqlite3GetVTable(X,Y) ((VTable*)0) +# define sqlite3VtabBeginParse(A, B, C, D, E) +# define sqlite3VtabFinishParse(X, Y) +# define sqlite3VtabArgInit(X) +# define sqlite3VtabArgExtend(X, Y) #else void sqlite3VtabClear(sqlite3 *db, Table*); void sqlite3VtabDisconnect(sqlite3 *db, Table *p); @@ -4408,7 +4412,6 @@ void sqlite3AutoLoadExtensions(sqlite3*); void(*)(void*) ); # define sqlite3VtabInSync(db) ((db)->nVTrans>0 && (db)->aVTrans==0) -#endif int sqlite3VtabEponymousTableInit(Parse*,Module*); void sqlite3VtabEponymousTableClear(sqlite3*,Module*); void sqlite3VtabMakeWritable(Parse*,Table*); @@ -4421,6 +4424,7 @@ int sqlite3VtabCallConnect(Parse*, Table*); int sqlite3VtabCallDestroy(sqlite3*, int, const char *); int sqlite3VtabBegin(sqlite3 *, VTable *); FuncDef *sqlite3VtabOverloadFunction(sqlite3 *,FuncDef*, int nArg, Expr*); +#endif sqlite3_int64 sqlite3StmtCurrentTime(sqlite3_context*); int sqlite3VdbeParameterIndex(Vdbe*, const char*, int); int sqlite3TransferBindings(sqlite3_stmt *, sqlite3_stmt *); -- 2.20.1.windows.1.3.g039c8793a4 This email and any files transmitted with it are confidential material. They are intended solely for the use of the designated individual or entity to whom they are addressed. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, use, distribution or copying of this communication is strictly prohibited and may be unlawful. If you have received this email in error please immediately notify the sender and delete or destroy any copy of this message ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
