upsert from select

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

upsert from select

Eric Grange-3
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: upsert from select

R Smith-2
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: upsert from select

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)

> 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 <[hidden email]> 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: upsert from select

R Smith
On 2018/11/30 12:50 PM, Eric Grange wrote:
> Apparently adding just a "WHERE 1" clause is enough...

Indeed, glad it works.

>
>> 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!


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