Thanks! Apparently adding just a "WHERE 1" clause is enough, ie. this passes
INSERT INTO vocabulary(word, count) SELECT * FROM (SELECT 'jovial', 1) WHERE 1 ON CONFLICT(word) DO UPDATE SET count=count+1 and the "WHERE 1" also makes the query with a json_each pass (not just in the snippet I posted, but also in the more complex I am actually using) > PS: I used "wcount" rather because "count" is an internal SQL function. Indeed, though it seems to be accepted here, I am not using a field with my name in may actual code. I only used it because that was in the example I copy-pasted from the SQL doc, I guess the doc could be updated (it's in https://sqlite.org/lang_UPSERT.html) Le ven. 30 nov. 2018 à 11:05, R Smith <ryansmit...@gmail.com> a écrit : > This does seem like a small bug. > > While the SQLite devs are having a look, this Zero-cost work-around > might suit your needs: > Simply add a WHERE clause, for example: > > CREATE TABLE vocabulary ( > word TEXT NOT NULL PRIMARY KEY, > wcount INT DEFAULT 1 > ); > > WITH A(w) AS ( > SELECT 'jovial' UNION ALL > SELECT 'jovial' > ) > INSERT INTO vocabulary(word) > SELECT w FROM A WHERE 1 > ON CONFLICT(word) DO UPDATE SET wcount=wcount+1 > ; > > > SELECT * FROM vocabulary > > -- word | wcount > -- ------ | ------------ > -- jovial | 2 > > > PS: I used "wcount" rather because "count" is an internal SQL function. > > > On 2018/11/30 11:14 AM, Eric Grange wrote: > > Hi, > > > > I am running into a little trouble when trying to use and "upsert" from a > > select clause. > > > > Starting from the "vocabulary" exemple at > > https://sqlite.org/lang_UPSERT.html this works > > > > INSERT INTO vocabulary(word, count) > > SELECT 'jovial', 1 > > ON CONFLICT(word) DO UPDATE SET count=count+1 > > > > > > but as soon as the "SELECT" has a from clause it does not seem to be > > working (near "DO": syntax error) > > > > INSERT INTO vocabulary(word, count) > > SELECT * FROM (SELECT 'jovial', 1) > > ON CONFLICT(word) DO UPDATE SET count=count+1 > > > > > > (without the ON CONFLICT clause the above is accepted) > > > > I have tried to place the SELECT between parenthesis, but SQLite then > > complains of an error on the opening parenthesis. > > Any workarounds ? > > > > My actual usage case would actually use a json_each to provide the values > > (in case that throws an extra spanner...), like in > > > > INSERT INTO vocabulary (word, count) > > SELECT atom, 1 from json_each('["alpha","beta"]') > > ON CONFLICT(word) DO UPDATE SET count=count+1 > > > > > > Eric > > _______________________________________________ > > 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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
