Re: [sqlite] Multi table select

Guy (Hotmail) wrote: > I think that: > Nevermind.. I figured it out. > > I needed a left join. > > does not cut it as a sollution. We new bees whant a complete solution to > learn. > Like the complete sql statement that you used to solve your probleme. select table1.id, table2.date from table1

Re: [sqlite] Multi table select

Hi, I think that: Nevermind.. I figured it out. I needed a left join. does not cut it as a sollution. We new bees whant a complete solution to learn. Like the complete sql statement that you used to solve your probleme. thanks Guy -- From: "tafte

Re: [sqlite] Multi table select

Nevermind.. I figured it out. I needed a left join. taftech wrote: > > I have two tables that I want to select data from > > For example > table1 has one column "id" which has 100 entries > > table2 has two column "id" and "date" > the "id" columns match > table2 has 1000 entries, but

[sqlite] Multi table select

I have two tables that I want to select data from For example table1 has one column "id" which has 100 entries table2 has two column "id" and "date" the "id" columns match table2 has 1000 entries, but only 50 of them match with table1 is there a way I can do a single select that will pull al

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

On 5 Aug 2010, at 10:03pm, Sam Roberts wrote: > But do you think the section would make the counting faster? I think > I'd have to get the row counts like this, which would still do the > slow full table scan: > > select section, count(*) from my_table where name like '%e%' group by > section;

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

On Thu, Aug 5, 2010 at 1:37 PM, Simon Slavin wrote: > > On 5 Aug 2010, at 8:42pm, Sam Roberts wrote: > >> select substr(name,1,1), count(*) from my_table where name like '%e%' >> group by substr(name,1,1); > > If you are constantly going to be using the first character of the name like > that, gi

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

On 5 Aug 2010, at 8:42pm, Sam Roberts wrote: > select substr(name,1,1), count(*) from my_table where name like '%e%' > group by substr(name,1,1); If you are constantly going to be using the first character of the name like that, give it a column of its own with its own index. Simon. __

[sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

I'd appreciate any suggestions on good ways to do this, I'm neither an SQL or sqlite expert, so I might be thinking about it all wrong. I have something like a (read-only) address book/rolodex, with interactive searching. As users type into the search box, I need to first know for each section how

[sqlite] data race seen in sqlite 3.6.16

Hi, I am using version 3.6.18 of sqlite and see the below data race (as shown by valgrind/Helgrind tool). Was wondering if this has been captured anywhere or a fix for it is available in the latest version 3.7 ( I was sort of considering an upgrade, if it included a fix for this). Any info on t

Re: [sqlite] Check for valid dates?

On 5 August 2010 16:28, K Peters wrote: > Hi all, > > is there a way to check for valid ISO 8601 dates? Something like: sqlite> select case theDate like '%T%' when 1 then replace( datetime( julianday( theDate ) ),' ','T') else datetime( julianday(

Re: [sqlite] Check for valid dates?

On 5 Aug 2010, at 4:28pm, K Peters wrote: > is there a way to check for valid ISO 8601 dates? Use a date library to turn your date into a number (e.g. epoch, Julian day), then turn it back into a string again. If you get back the same string you start with, it's valid. Simon. ___

[sqlite] Check for valid dates?

Hi all, is there a way to check for valid ISO 8601 dates? Thanks Kai ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Docs patch

The mailing list software (Mailman) strips attachments. On Thu, Aug 5, 2010 at 9:39 AM, Martin Jenkins wrote: > Hi, > > I sent a patch which fixes a fairly large number of typos in > the 3.7.0 HTML documentation to the list on the 25th July. > > It was sent from an unsubscribed address and apart

[sqlite] Docs patch

Hi, I sent a patch which fixes a fairly large number of typos in the 3.7.0 HTML documentation to the list on the 25th July. It was sent from an unsubscribed address and apart from a message saying it would need to be moderated, it appears to have vanished without trace. Is this the sort of patc

Re: [sqlite] Repairing a Database.

On 4 Aug 2010, at 10:15pm, Kirk Clemons wrote: > Also, does this mean that I could take a backup copy of my database and > import the data that is retrieved from the .dump command on the corrupt > database? We cannot tell what .dump will get from your old database because it is corrupt. It

Re: [sqlite] EXTERNAL: Process memory space exhausted in 3.7.0

Can you force a WAL checkpoint periodically? Also...of course test 3.7.0.1 PRAGMA database.wal_checkpoint; I assume you're doing your thing in one transaction. I would've thought WAL would notice that and do it's auto-checkpoint anyways. Can one of the experts comment on the expected behav

Re: [sqlite] Vacuum'ing database returns a 'PRIMARY KEY must be unique' error in 3.7.0

What happens if you do this with 3.6.23.1 or 3.7.0.1 ? Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Scott Crosby Sent: Wed 8/4/2010 8:14 AM To: sqlite

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

2010/8/5 Dominique Pellé : > Using information in previous reply, I can do it with 2 UPDATE queries > as follows (but I suspect that there is a better solution). > >  UPDATE t1 SET l_nm = (SELECT l_nm FROM t2 WHERE t1.ID_PK = t2.ID_FK) >  WHERE ID_PK IN (SELECT ID_FK FROM t2); > >  UPDATE t1 SET r_

Re: [sqlite] linux ubuntu 3.7.01 installs sqlite3 3.6.22

I learned something today :-) I added /usr/local/lib to my /etc/ld.so.conf then run "sudo ldconfig" then I could use the latest sqlite3 in my /usr/local/ directory ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailma

Re: [sqlite] Repairing a Database.

Also, does this mean that I could take a backup copy of my database and import the data that is retrieved from the .dump command on the corrupt database? If so how would I do this and get past the PRIMARY KEY/existing table errors? ~Kirk -Original Message- From: sqlite-users-boun...@sq

[sqlite] Process memory space exhausted in 3.7.0

Hello, The windows desktop application that I maintain uses sqlite for some of its storage. The data volume that we must handle has increased dramatically over the past 6 months and as it was to be expected update performance has degraded accordingly. Because of that, I was very quick to jump o

Re: [sqlite] Re Populating a database from a file

Also, forgot to mention that binary data will not normally work for csv format. It's best to leave the settings at their default and do; .mode list .separator | .output mytable.sql/.txt .dump mytable _ .read mytable.sql/.txt ~Kirk -Original Message- From: sqlite-users-boun...

Re: [sqlite] Re Populating a database from a file

.dump copies the insert statements from the table along with the data by doing a 'select *'. You will want to make sure you import or read the file with the same settings as the .dump was done in. .show will show the settings for mode, separator, etc. To export to a csv from a table might look s

Re: [sqlite] Populating a database from a file

There is the .read function which will read in a file that contains insert statements. This is done through the shell. ~Kirk -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of john knops Sent: Tuesday, August 03, 2010 6:53 AM To

Re: [sqlite] Repairing a Database.

Thank you Roger, I will look this over and see what I can do. ~Kirk Kirk Clemons Technical Support Analyst Chief Architect(r) 6500 N. Mineral Dr. Coeur d'Alene, Idaho 83815 Phone: (800)482-4433 (208)664-4204 Professional Software www.chiefarchitect.com Consumer Software www.HomeDe

[sqlite] Vacuum'ing database returns a 'PRIMARY KEY must be unique' error in 3.7.0

I decided to vacuum my places.sqlite database (used by Firefox to store its history and bookmarks) and got a primary key violation. The database passes the analyzer and dumper with no errors. select count(*) from the different tables also shows no errors. However, a dump and attempted restore DOE

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

Igor Tandetnik wrote: > Dominique Pellé wrote: >> For example, given 2 tables t1 and t2, both with 2 columns as follows... >> >> Table t1: >> >>    ID   name >>    --   >>    1     >>    2    NULL >>    3    NULL >>    4     >>    (~1 million records) >> >> Table t2: >> >>    ID   na

Re: [sqlite] linux ubuntu 3.7.01 installs sqlite3 3.6.22

... my fault, the programs are taking the lib from /usr/lib before /usr/local/lib it is not doing this way on my macosx, I am searching now how to specify to take first the /usr/local/lib when executing, even for /usr/local/bin/sqlite3 ___ sqlite-users ma

[sqlite] Extension for TCL evaluation by user-defined function inside SQLite

I have a lot of TCL language structures stored in my databases and want to get access to these from sqlite3 shell. So I write extension for this: .load ./libsqlitetcl.so -- TCL (cmd, argv) SELECT TCL('info patchlevel'); 8.5.8 SELECT TCL('return $argc','abba', 'baba'); 2

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

On Thu, Aug 5, 2010 at 01:17, Igor Tandetnik wrote: > > Or, if t1.ID is a primary key or otherwise has a unique constraint: > > insert or replace into t1(ID, name) > select ID, name from t2; this one is different because it would cause INSERTs into t1 if some ID exists in t2 and not t1. -- Ben