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 <david.raym...@tomtom.com> 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
