[sqlite] upsert from select

[Eric Grange]

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

[R Smith]

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.

Post by Eric Grange
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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Eric Grange]

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)

Post by R Smith
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)

Post by R Smith
This does seem like a small bug.
While the SQLite devs are having a look, this Zero-cost work-around
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.

Post by Eric Grange
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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[R Smith]

Apparently adding just a "WHERE 1" clause is enough...

Indeed, glad it works.

Post by R Smith
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)

Apologies, I wasn't judging your use of it (or the documentation's),
just explaining why my example deviated from yours in that regard.

They can and do definitely work in many cases, such as the above, and
even when they don't work, simply enclosing in double-quotes will fix
it.  Some people's answer to this is to always use the quotes, my
approach is to avoid reserved words/functions out of principle - a
practice I'm prepared to admit is probably the worse of the choices,
because a word might become reserved over some years of development
[think WITH, UNLIKELY, DO,  etc.] which may render older SQL using any
of those: "unsafe", whereas double-quoting is future-proof.

But what can I say? I live dangerously!