Upsert syntax question

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

Upsert syntax question

David Raymond
Maybe it's just Friday, but I can't figure out what I'm messing up here. Would some kind soul point me to where I'm messing up?

Version 3.25.2

sqlite> create table ids (id int primary key, in_old bool, in_new bool);
QUERY PLAN
`--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?)
Run Time: real 0.234 user 0.000000 sys 0.109201

sqlite> insert into ids (id, in_old) select distinct id, 1 from gc_old;
QUERY PLAN
|--SCAN TABLE gc_old USING COVERING INDEX idx_gc_old_id
`--USE TEMP B-TREE FOR DISTINCT
Run Time: real 18.050 user 10.530067 sys 1.279208

sqlite> insert into ids (id, in_new) select distinct id, 1 from gc_new on conflict (id) do update set in_new = 1;
Run Time: real 0.000 user 0.000000 sys 0.000000
Error: near "do": syntax error
_______________________________________________
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: Upsert syntax question

Simon Slavin-3
On 19 Oct 2018, at 5:24pm, David Raymond <[hidden email]> wrote:

> sqlite> insert into ids (id, in_new) select distinct id, 1 from gc_new on conflict (id) do update set in_new = 1;

You can't supply a statement for ON CONFLICT.  It can only trigger IGNORE / FAIL / ABORT / ROLLBACK / REPLACE.

<https://sqlite.org/lang_conflict.html>

If you need anything more sophisticated, you might like to make a TRIGGER.

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: Upsert syntax question

David Raymond
But isn't that the whole point of the whole UPSERT thing?

https://www.sqlite.org/lang_UPSERT.html

...

Ok, found my problem though
https://www.sqlite.org/lang_insert.html

To avoid a parsing ambiguity, the SELECT statement should always contain a WHERE clause, even if that clause is simply "WHERE true", if the upsert-clause is present. Without the WHERE clause, the parser does not know if the token "ON" is part of a join constraint on the SELECT, or the beginning of the upsert-clause.


sqlite> insert into ids (id, in_new) select distinct id, 1 from gc_new where true on conflict (id) do update set in_new = 1;
QUERY PLAN
|--SCAN TABLE gc_new USING COVERING INDEX idx_gc_new_id
`--USE TEMP B-TREE FOR DISTINCT
Run Time: real 30.066 user 14.757695 sys 2.667617



-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Simon Slavin
Sent: Friday, October 19, 2018 12:36 PM
To: SQLite mailing list
Subject: Re: [sqlite] Upsert syntax question

On 19 Oct 2018, at 5:24pm, David Raymond <[hidden email]> wrote:

> sqlite> insert into ids (id, in_new) select distinct id, 1 from gc_new on conflict (id) do update set in_new = 1;

You can't supply a statement for ON CONFLICT.  It can only trigger IGNORE / FAIL / ABORT / ROLLBACK / REPLACE.

<https://sqlite.org/lang_conflict.html>

If you need anything more sophisticated, you might like to make a TRIGGER.

Simon.
_______________________________________________
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