Re: [sqlite] [sqlite-dev] having problems with "select where not exists"
Forwarded to the sqlite-users mailing list ... Probably the spelling error ... (mf2 does not exist cuz you typed my2 as the alias) >1) One problem is that there is no column name when I include the >"as" phrase in the select statment as in >select * from myfile as mf1... >where not exists >( select column name from myfile as my2 >when mf1.column = mf2.column...) Because otherwise "cannot reproduce" ... and without the spelling errors it works perfectly. If you are just "winging it" then please "wing it" in the command line shell and paste (attachements not permitted) the complete transcript including the version number being used and the commands given and results obtained, together with your desription of what you think the results ought to have been (and why you think that to be the case). --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-dev [mailto:sqlite-dev-boun...@mailinglists.sqlite.org] >On Behalf Of Steve Leonard >Sent: Friday, 30 November, 2018 08:13 >To: sqlite-...@mailinglists.sqlite.org >Subject: [sqlite-dev] having problems with "select where not exists" > >I have sql that contains a "not exist" clause, yet the same SQL runs >on >other databases. > > >I have looked for an SQL manual, but all I could find was a zip file >containing a bunch of HTML files, and I cannot find a way to search >through all of them. > >1) One problem is that there is no column name when I include the >"as" >phrase in the select statment as in > >select * from myfile as mf1... > >where not exists > >( select column name from myfile as my2 > >when mf1.column = mf2.column...) > > >2) I have posted another question to the mailing list but have not >gotten a way to see all the questions I have posted (this is the 2nd) >and see > >the replies, if any. > >Please advise, thank you. > >Steve > > > > > >___ >sqlite-dev mailing list >sqlite-...@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-dev ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Monitoring SQLite Web Site
This email is more to Dr Hipp, but, there are other members of this list that might also benefit from this site I'm about to mention. I know there are many out there, but monitoring 50 sites for free for their up-time is kind of nice. Right up front, I have exactly ZERO affiliation with the product I'm about to mention. I have no financial backing from, or towards this product. I've never spoken to anyone there, or no one from there has reached out to me. This is an opinion of my own. It's a tool we're using here at work, and I've just started to use to monitor my home servers as a method to monitor when my ISP decides to shut me down randomly at night. In the past, there have been reports about the SQLite sites being unavailable. It's unfortunate, but it happens to everyone. Instead of having someone from the user list report problems, maybe use https://www.freshworks.com/website-monitoring/ to get an email report out to the admins immediately? Freshping checks the site you specify once per minute, and throws a report when down. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Misleading error message on missing function
sqlite> select json_each('[1, 3, 5]');
Error: no such function: json_each
sqlite> select * from json_each('[1,2]');
0|1|integer|1|1||$[0]|$
1|2|integer|2|2||$[1]|$
Misusing the json_each() table-values function as a regular function
yields a misleading (IMHO) "no such function" message, despite the
function (aka eponymous vtable) actually existing.
Any chance we might get a more user-friendly error message?
Like perhaps "Error: table-valued function not usable here: json_each"?
Thanks, --DD
PS: Does pragma function_list lists such table-valued functions?
Can't say easily, since not available in pre-built binary apparently.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Support function_list in pre-built binaries from SQLite download page
sqlite> pragma function_list; sqlite> Nothing returned, so obviously the -DSQLITE_INTROSPECTION_PRAGMAS compile-time option is not used [1]. Could it be ON by default please? TIA, --DD [1] https://www.sqlite.org/pragma.html#pragma_function_list ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Updating Folder_Path in Windows 10 Photos App
On 29 Nov 2018, at 7:32pm, Carlo capaldo wrote: > select folder.Folder_Path, > replace(Folder_Path,'C:\Users\carlo\Pictures','E:\Photos') > from Folder > where folder.Folder_Id = 1 > > Could someone kindly educate me on how to update the folder references? My guess is that you actually want UPDATE folder SET Folder_Path = 'E:\Photos' WHERE Folder_Path = 'C:\Users\carlo\Pictures' AND folder_Id = 1 Please take a backup copy of your database file before trying this. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_bind_text() and WHERE x IN (?)
On Fri, Nov 30, 2018 at 3:26 PM Simon Walter wrote: > On 11/30/2018 11:20 PM, Dominique Devienne wrote: > To be honest, I am using apr_dbd as I would like to support more than > just SQLite. So I will need to play around with MySQL and PostgreSQL at > least and maybe branch if SQLite is in use. I have no idea yet if MySQL > and/or PostgreSQL can handle this scenario and how they do it. > PostgreSQL has native array support. Also has JSON support. So I'm fairly sure both a possible with PG, except with different syntaxes of course. Don't know about MySQL. Oracle has a VARRAY datatype, and you use the TABLE() operator to turn its content into a table-values "thing", so you can write WHERE c in (TABLE(:1)) and bind the VARRAY, which you've built-up "client-side". BTW, the fact there's no "client-side" with SQLite is typically used as an argument for not supporting "natively" binding the RHS of the WHERE IN clause, and that the work-arounds are good enough. Obviously I disagree :). --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_bind_text() and WHERE x IN (?)
On 11/30/2018 11:20 PM, Dominique Devienne wrote:
> On Fri, Nov 30, 2018 at 3:03 PM Dominique Devienne
> wrote:
>
>> On Fri, Nov 30, 2018 at 2:44 PM Richard Hipp wrote:
>>
>>> On 11/30/18, Simon Walter wrote:
Thanks Dominique,
Much appreciated. I can now stop pulling out my hair. I will do
something with sprintf.
>>>
>>> See https://www.sqlite.org/carray.html
>>
>>
>> Right. Any table-valued function would do too.[...]
>>
> In fact, it's probably possible right now via the
>> JSON1 extension, if your text value is JSON-formatted.
>>
>
> Yep, works fine, as expected. So that's another possibility too.
> Assuming you can use the JSON1 extension and it's enabled in the SQLite
> DDL. --DD
>
> sqlite> .header on
> sqlite> create table t (c, n);
> sqlite> insert into t values (1, 'one'), (2, 'two'), (3, 'three');
> sqlite> select n from t where c in (select value from json_each('[1, 3]'));
> n
> one
> three
> sqlite> select n from t where c in (select value from json_each('[]'));
> sqlite> select n from t where c in (select value from json_each('[2]'));
> n
> two
> sqlite>
>
I suppose an array of ints or an array of pointers to \000 terminated
char arrays or any other kind of array could be escaped correctly
provided the caller give some kind of hint as to what the type of data is.
To be honest, I am using apr_dbd as I would like to support more than
just SQLite. So I will need to play around with MySQL and PostgreSQL at
least and maybe branch if SQLite is in use. I have no idea yet if MySQL
and/or PostgreSQL can handle this scenario and how they do it. Though
the possibility for SQL injections is nil, as the comma separated list
is generated by the application and not user input... famous last words?
I must check again.
Interesting tidbit:
...WHERE id IN ("1") actually works. As soon as there is a comma, SQLite
returns 0 rows.
I will keep hacking. Thanks for the advice! Much appreciated.
Simon
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_bind_text() and WHERE x IN (?)
On 2018/11/30 2:52 PM, Simon Walter wrote:
I am trying to make a prepared statement that has a parameter such as
"1, 893, 121212". obviously it is text (a string) but it cannot be
quoted or the result will be:
SELECT id, data FROM val WHERE id IN ("1, 893, 121212");
I understand normally I would need the quotes, such as:
SELECT id, name, val FROM obj WHERE name = ?
There is one slightly convoluted but easy copy-paste solution - if this
is not part of a very cpu-intensive query and you just want an easy way
to get this done this one time...
The following Query will do exactly what you need:
(It's simply a CTE that unravels the comma-separated format line you
give in ? into a table form and then uses that to do the lookup with)
WITH csvrec(i, l, c, r) AS (
SELECT 1, 1, ?||',', ''
UNION ALL
SELECT i,
instr( c, ',' ) AS vLength,
substr( c, instr( c, ',' ) + 1) AS vRemainder,
trim( substr( c, 1, instr( c, ',' ) - 1) ) AS vCSV
FROM csvrec
WHERE vLength > 0
)
SELECT id, data
FROM val
WHERE id IN (SELECT r FROM csvrec WHERE r <> '');
;
-- The "... WHERE r <> '' " bit might not be needed here depending on your use
case.
This one you can run straight in sqlite to more clearly see what is happening
inside the CTE bit using your '1, 893, 121212' example (for fun):
WITH csvrec(i, l, c, r) AS (
SELECT 1, 1,'1, 893, 121212'||',', ''
UNION ALL
SELECT i,
instr( c, ',' ) AS vLength,
substr( c, instr( c, ',' ) + 1) AS vRemainder,
trim( substr( c, 1, instr( c, ',' ) - 1) ) AS vCSV
FROM csvrec
WHERE vLength > 0
)
SELECT * FROM csvrec
;
Disclaimer: This comes as part of the sqlitespeed install example
scripts and as such are simplified to handle basic Comma-separated text
only and does not cover all the very complex multi-quoted, multi-line
stuff that might be found in a complex CSV data file as described in
RFC4180 - so as long as you control the format of the "csv" input text,
all is well.
Cheers,
Ryan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_bind_text() and WHERE x IN (?)
On Fri, Nov 30, 2018 at 3:03 PM Dominique Devienne
wrote:
> On Fri, Nov 30, 2018 at 2:44 PM Richard Hipp wrote:
>
>> On 11/30/18, Simon Walter wrote:
>> > Thanks Dominique,
>> >
>> > Much appreciated. I can now stop pulling out my hair. I will do
>> > something with sprintf.
>>
>> See https://www.sqlite.org/carray.html
>
>
> Right. Any table-valued function would do too.[...]
>
In fact, it's probably possible right now via the
> JSON1 extension, if your text value is JSON-formatted.
>
Yep, works fine, as expected. So that's another possibility too.
Assuming you can use the JSON1 extension and it's enabled in the SQLite
DDL. --DD
sqlite> .header on
sqlite> create table t (c, n);
sqlite> insert into t values (1, 'one'), (2, 'two'), (3, 'three');
sqlite> select n from t where c in (select value from json_each('[1, 3]'));
n
one
three
sqlite> select n from t where c in (select value from json_each('[]'));
sqlite> select n from t where c in (select value from json_each('[2]'));
n
two
sqlite>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_bind_text() and WHERE x IN (?)
On Fri, Nov 30, 2018 at 2:44 PM Richard Hipp wrote: > On 11/30/18, Simon Walter wrote: > > Thanks Dominique, > > > > Much appreciated. I can now stop pulling out my hair. I will do > > something with sprintf. > > See https://www.sqlite.org/carray.html Right. Any table-valued function would do too. carray() is a dangerous one IMHO, since you expose a pointer to memory, and must be certain to parameter it correctly so it "interprets" the C-heap memory correctly, and does not read past-the-array-end. It's also a security risk. A safer alternative would be another table-valued function, which parses a string and returns the values as some primitive type. In fact, it's probably possible right now via the JSON1 extension, if your text value is JSON-formatted. carray() will be faster of course. But a text-parsing table-valued function would work well too. FWIW, I've been asking for the ability to bind "officially" arrays for a while :). Just look at the ML archive. Or, failing that, at least use sqlite3_mprintf() rather than > sprintf(). https://www.sqlite.org/printf.html Would that work here, when wanting to "paste" *several* values? Preventing SQL injections by proper escaping works for "scalar" values, no? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_bind_text() and WHERE x IN (?)
On 11/30/18, Simon Walter wrote: > Thanks Dominique, > > Much appreciated. I can now stop pulling out my hair. I will do > something with sprintf. See https://www.sqlite.org/carray.html Or, failing that, at least use sqlite3_mprintf() rather than sprintf(). https://www.sqlite.org/printf.html -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Updating Folder_Path in Windows 10 Photos App
Hi Carlo, Attachments are stripped on this forum - could you use a file service or upload it somewhere? Also be clear about the which paths all changed and to what - AND make a backup of the file before fixing it - but there most certainly is some SQL that will fix what you described. Cheers, Ryan On 2018/11/29 9:32 PM, Carlo capaldo wrote: Dear Users, Windows 10 Photos App is no longer able to find my files when opening the app. I discovered that the App uses Sqlite and using DB Browser for SQLite I was able to display the Table folder which shows the many different folder paths I believe the App is referencing (see attached). The original folder paths have all changed and the photos etc have been consolidated into a single directory (E:\Photos). I tried using the following sql commands to change the folder references for each entry. The sequence appears to execute correctly but I can’t find a way to update or write the database. select folder.Folder_Path, replace(Folder_Path,'C:\Users\carlo\Pictures','E:\Photos') from Folder where folder.Folder_Id = 1 Could someone kindly educate me on how to update the folder references? Kind regards, Carlo. ___ 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
Re: [sqlite] sqlite3_bind_text() and WHERE x IN (?)
Thanks Dominique, Much appreciated. I can now stop pulling out my hair. I will do something with sprintf. Best regards, Simon On 11/30/2018 10:37 PM, Dominique Devienne wrote: > On Fri, Nov 30, 2018 at 2:10 PM Simon Walter wrote: > >> How does one use WHERE x IN (?) with a prepared statement? What is the >> correct way to do this? >> > > You cannot do it. Must use WHERE x IN (?, ?, ?), i.e. an explicit and > known in advance > number of bind placeholders. Or not use binding at all, and "paste" your > text value before > preparing the statements. --DD > ___ > 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
Re: [sqlite] sqlite3_bind_text() and WHERE x IN (?)
On Fri, Nov 30, 2018 at 2:10 PM Simon Walter wrote: > How does one use WHERE x IN (?) with a prepared statement? What is the > correct way to do this? > You cannot do it. Must use WHERE x IN (?, ?, ?), i.e. an explicit and known in advance number of bind placeholders. Or not use binding at all, and "paste" your text value before preparing the statements. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_bind_text() and WHERE x IN (?)
I am trying to make a prepared statement that has a parameter such as
"1, 893, 121212". obviously it is text (a string) but it cannot be
quoted or the result will be:
SELECT id, data FROM val WHERE id IN ("1, 893, 121212");
I understand normally I would need the quotes, such as:
SELECT id, name, val FROM obj WHERE name = ?
I am not sure if this is happening. From a few tests, it seems to be
what is going on.
How does one use WHERE x IN (?) with a prepared statement? What is the
correct way to do this?
Thanks for your time.
Best regards,
Simon
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] upsert from select
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Updating Folder_Path in Windows 10 Photos App
Dear Users, Windows 10 Photos App is no longer able to find my files when opening the app. I discovered that the App uses Sqlite and using DB Browser for SQLite I was able to display the Table folder which shows the many different folder paths I believe the App is referencing (see attached). The original folder paths have all changed and the photos etc have been consolidated into a single directory (E:\Photos). I tried using the following sql commands to change the folder references for each entry. The sequence appears to execute correctly but I can’t find a way to update or write the database. select folder.Folder_Path, replace(Folder_Path,'C:\Users\carlo\Pictures','E:\Photos') from Folder where folder.Folder_Id = 1 Could someone kindly educate me on how to update the folder references? Kind regards, Carlo. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] upsert from select
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 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
Re: [sqlite] Detecting erroneous multi-row subqueries
On Fri, Nov 30, 2018 at 3:00 AM J. King wrote: > On 2018-11-29 20:56:13, "Richard Hipp" wrote: > >On 11/29/18, J. King wrote: > >> Is it possible to make SQLite fail like PostgreSQL does? > > > >That is possible in theory, but how many of the millions of existing > >applications would that break? > > > > I was wondering more if it's -currently- possible via some switch I'm > not aware of. As an enhancement obviously I wouldn't expect it as a > default (not before SQLite 4, anyway), but as a debugging feature like > reverse_unordered_selects, it might help to uncover millions of bugs. ;) > +1. There are many SQLite gotchas for backward compatibility reasons. Regularly those come back up on this list, because someone's been bitten by it. Gotcha is explained, BC is invoked for not fixing it, pragma is suggested on an opt-in basis to have a "stricter and safer" SQLite, then nothing happens. Some gotchas require file-format changes, so are mostly off-limit completely. Although myself I'd welcome a new and non-BC format, getting rid of all legacy. DRH has expressed several times he's not willing to go there, that's just life :) But when the gotcha is pure runtime, it's less easy to accept the status quo, I have to be honest. Especially since Richard often does not take the time to explain his rational for not doing them. It's probably obvious to him, from cost of implementation, or maintenance/testing (to maintain 100% line/branch coverage), or even from disagreeing with the proposed features for such or such reasons. But it's not always obvious to me for sure, and I confess to difficulty accepting the silence. I realise it's a bit unfair to blame Richard when he gives away SQLite, such a wonderful library, and that he's super busy, creating Fossil, and forums, and SMTP servers, and what-not we are not even aware of, for his commercial clients. Yet I still wish there was a bit more transparency and discussions around SQLite, with the "real" stakeholders of SQLite, i.e. DRH and his small dev team. We do discuss things at length here, but the only authoritative voices are Richard and Dan, no offence to all the other great contributors on this list, and Richard and Dan are often making themselves scarce in these threads. I just wish they got involved more, and shared more insights and rationals. Xmas is around the corner, so it's that time of the year I guess :). I'll shut up now. Sorry for the noise. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] upsert from select
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] upsert from select
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
