[sqlite] UPSERT

mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Benoit Mortgat

[sqlite] SQLite lets selecting a column that is not in the Group by clause

or an unknown one? If this is a feature, is there a pragma to forbid such a syntax? Have a nice day -- Benoit Mortgat 20, avenue Marcel Paul 69200 Vénissieux, France +33 6 17 15 41 58 +33 4 27 11 61 23 ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Query critique

end) = upper(a.name)) ORDER BY score DESC", 0, > %client.username, %globId, %client.username); > Create an index either on player column or friend column in your second table. CREATE INDEX i_friendTable ON friendTable (player ASC); -- Benoit Mortga

Re: [sqlite] count distinct group by

ere s in > (select s from mytable >  group by s >  having min(r1)=max(r1) and min(r2)=max(r2) > ); Another possibility: having count(distinct r1) = 1 and count(distinct r2) = 1 -- Benoit Mortgat 20, avenue Marcel Paul 69200 Vénissieux, France +33 6 17 15 41 58 +33 4 27 11 61 23 _

Re: [sqlite] How to update many rows efficiently?

some ID exists in t2 and not t1. -- Benoit Mortgat 20, avenue Marcel Paul 69200 Vénissieux, France +33 6 17 15 41 58 +33 4 27 11 61 23 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to update many rows efficiently?

d rows, but only keep existing ones, and id_pk must be declared as primary key. Regards, -- Benoit Mortgat 20, avenue Marcel Paul 69200 Vénissieux, France +33 6 17 15 41 58 +33 4 27 11 61 23 ___ sqlite-users mailing list sqlite-users@sqlite.org http://s

[sqlite] Upon table creation, multiple CHECK constraints do not work with FTS3

SQLite normally does according to http://osdir.com/ml/sqlite-users/2009-04/msg00279.html using the R*Tree module this syntax works, so the bug should be in the FTS3 module. Thank you, Benoit -- Benoit Mortgat 20, avenue Marcel Paul 69200 Vénissieux, France +33 6 17 15 41 58 +33 4 27 11 61 23

Re: [sqlite] Upon table creation, multiple CHECK constraints do not work with FTS3

lk1...@gmail.com> wrote: > > On Aug 30, 2010, at 12:22 PM, Benoit Mortgat wrote: > > > Hello, > > > > The following table creation fails under latest release : > > > > CREATE VIRTUAL TABLE foo USING FTS3 > > ( > > bar TEXT, othercolumns TEXT, >

Re: [sqlite] DB file locked when no other process using it

qlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Benoit Mortgat 20, avenue Marcel Paul 69200 Vénissieux, France +33 6 17 15 41 58 +33 4 27 11 61 23 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] what could be the reason that natural join stops working ?

empty string (while the string shouldn't be empty). > > I test that by doing an explicit join > > select Header from vraag > join vraaglist on vraag.vlid = vraaglist.vlid > where Nr = 0 and vraaglist.Name = 'eortc_br23' > Are you sure that after altering your tables adding columns,

[sqlite] SQLite 3.7.5 : same query written using NOT IN and EXCEPT does not give out same results

Hello, I have come across a strange behaviour of SQLite 3.7.5. The following query: SELECT DISTINCT COALESCE(a.xxx, b.yyy) value FROM tbl1 a LEFT OUTER JOIN tbl2 b ON a.zzz = b.ttt EXCEPT SELECT DISTINCT ggg value FROM tbl3; will not return any results (which seems to be correct).

Re: [sqlite] SQLite 3.7.5 : same query written using NOT IN and EXCEPT does not give out same results

On Tue, Feb 22, 2011 at 14:17, Richard Hipp wrote: > > The query is really more like this: > > SELECT DISTINCT COALESCE(a.xxx, b.value) value >  FROM tbl1 a >  LEFT OUTER JOIN tbl2 b >    ON a.zzz = b.ttt >  WHERE value NOT IN ( >        SELECT DISTINCT ggg >          FROM tbl3 >

[sqlite] SQLite shell dot commands

and to escape the table name in the dot-commands? Potential problem: how to use .backup or .restore with a file name that contains both ' " and space? Thanks. -- Benoit Mortgat ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] double_quote(text_field)

Watkinson wrote, On 2/22/2012 5:33 PM: >> >> Did you mean something like >> >> SELECT '"'||text_field||'"' FROM table; >> >> so that your column's data is quoted? -- Benoit Mortgat ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite shell dot commands

ot planned to rewrite the argument tokenizer for meta-commands could it be possible at least to specify in the output of .help how those arguments are tokenized? (Note: another workaround to import into "Ben's table" is using the octal digits escaping with backslashes:) .import my_fi

Re: [sqlite] SQLite shell dot commands

convert my thread to a debate about what characters table names should include. -- Benoit Mortgat ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressing BLOB

80/cgi-bin/mailman/listinfo/sqlite-users -- Benoit Mortgat 20, avenue Marcel Paul 69200 Vénissieux, France +33 6 17 15 41 58 +33 4 27 11 61 23 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TEXT PRIMARY KEY

th a ";" sqlite> SELECT CASE WHEN NULL = NULL THEN 0 ...> WHEN NULL <> NULL THEN 1 ...> ELSE 2 ...>END; 2 sqlite> .q -- Benoit Mortgat ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Efficient random sampling in a large table using builtin functions.

conditions. -- Benoit Mortgat ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Efficient random sampling in a large table using builtinfunctions.

On Thu, Mar 8, 2012 at 15:02, Igor Tandetnik <itandet...@mvps.org> wrote: > Benoit Mortgat <mort...@gmail.com> wrote: >> * Generate N, random, row numbers between 1 and (SELECT COUNT(*) FROM >>   the_table_name). Maybe using remainder operator % and builtin ABS() >