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
