Re: [sqlite] 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:sqlite-users-boun...@mailinglists.sqlite.org] 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  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
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

Re: [sqlite] Upsert syntax question

[Simon Slavin]

On 19 Oct 2018, at 5:24pm, David Raymond  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.



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

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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.00 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.00 sys 0.00
Error: near "do": syntax error
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users