Re: [sqlite] Query question

2011-05-07 Thread cmartin
On Fri, 6 May 2011, Matthew Jones wrote: > sqlite> create table a (a, b); > sqlite> insert into a values (10, 1); > sqlite> insert into a values (10, 2); > sqlite> insert into a values (10, 2); > sqlite> insert into a values (11, 2); > sqlite> insert into a values (11, 2); > sqlite> insert into a

Re: [sqlite] Noobie question about importing data into table

2010-07-13 Thread cmartin
On Tue, 13 Jul 2010, Calimeron wrote: > > Can somebody point me in the right direction? > > I have joined the two tables by doing: select * from ChineseTable, > EnglishTable on IDX_c = IDX_cedict > > Now I would like to save this as a new table, so I can manipulate this table > in sqlite

Re: [sqlite] (no subject)

2010-05-22 Thread cmartin
On Sat, 22 May 2010, Prakash Reddy Bande wrote: > Me too, all attempts to unsubscribe have failed. On Sat, 22 May 2010, wrote: > please unuscribed Did you try this: Enter the email address

Re: [sqlite] famous "constraint failed" error

2010-01-22 Thread cmartin
On Fri, 22 Jan 2010, Jan Bilek wrote: > processing > of write transaction fails with strange "constraint failed" error (no. 19). > I've tried to search the web for any solution, but i haven't found > anything. While a web search can be useful, it is often more useful to go straight to the

Re: [sqlite] Tedious CSV import question

2009-09-24 Thread cmartin
On Thu, 24 Sep 2009, Rich Shepard wrote: > On Thu, 24 Sep 2009, C. Mundi wrote: > >> I just bit the bullet and did it. Python has an excellent csv module, >> capable of handling just about any dialect you're likely to encounter. I >> am so grateful I did not have to write a parser for CSV. In

Re: [sqlite] Subtotal SQL

2009-07-29 Thread cmartin
On Wed, 29 Jul 2009, P Kishor wrote: For my part, I don't know how to do a running total in a result set unless I have some kind of a counter that keeps track of the "row before the current row" This is also the only way I know of. Chris On Wed, Jul 29, 2009 at 8:50 AM, Rich

Re: [sqlite] Ability to convert Access to SQLite

2009-07-22 Thread cmartin
On Wed, 22 Jul 2009, scabral wrote: > i am new to SQLite and i currently have an access database that i would like > to convert over to a SQLite database. > > 1. Need to automatically import text file into SQLite database on local > machine (machine always on). I was thinking of using scheduled

Re: [sqlite] New Book Available

2009-07-09 Thread cmartin
On Thu, 9 Jul 2009, Rich Shepard wrote: > Rick van der Laans, who wrote the excellent "Introduction to SQL, 4th Ed." > (and eariler editions, of course) has just had his new book specific to > SQLite published. It is another resource for those who want a detailed > explanation of how to get the

Re: [sqlite] Order by term not in result set

2009-06-09 Thread cmartin
On Tue, 9 Jun 2009, Simon Slavin wrote: >> SELECT id, url, selected, name FROM db1.test UNION >> SELECT id, url, selected, name FROM db2.test >> ORDER BY name ASC, id DESC LIMIT 100" > > I would add to Marin's answer that reading the above, I have no idea > which database the column 'name'

Re: [sqlite] About Time Field

2009-05-15 Thread cmartin
On Fri, 15 May 2009, Hughman wrote: >> There is no Time type in SQLite. > > Oops... I use Sqlite Administrator to create a table , and the datatypes are > almost as many as MySQL , such as Date, Time, TimeStamp, varchar. > Since sqlite only has 5 kinds of datatype, why doesn't it throw a error >

Re: [sqlite] Slim down join results (all fields returned)

2009-04-17 Thread cmartin
On Fri, 17 Apr 2009, flakpit wrote: > Currently, I return any needed data like this. > > select * from pubs,notes,publishers where pub_title like '%salem%' > and pubs.note_id=notes.note_id > and pubs.publisher_id=publishers.publisher_id > > And it works except for all fields in the matching

Re: [sqlite] delete with an "exists" clause

2009-04-09 Thread cmartin
On Thu, 9 Apr 2009, Dave Dyer wrote: > This little program deletes all rows. Is this a bug, or > perhaps I misunderstand how delete with an exists clause > is supposed to work. > > drop table if exists dummy; > create table dummy ( var int); > insert into dummy (var) values (1); > insert into

Re: [sqlite] Creating a secondary table automatically

2009-03-10 Thread cmartin
On Tue, 10 Mar 2009, Erik Smith wrote: > I am new to SQLite and am trying to automatically create a secondary table > which my python app will query against. The secondary table is a summary of > the 1st table by specific types. I have looked at stored procedures (but > sqlite does not support

Re: [sqlite] CURRENT_DATE Behavior

2008-10-21 Thread cmartin
On Tue, 21 Oct 2008, jonwood wrote: > mikewhit wrote: >> >> Using UTC in the DB stops you going mad when something happens >> on the DST changeover (localtime hours vanish, or happen twice), >> or you have systems running in or across different countries. >> >> It also means you can subtract two

Re: [sqlite] How to use PRIMARY KEY AUTOINCREMENT when modellingweakentities?

2008-09-05 Thread cmartin
I suspect somes confusion is at work here because the books you refer to which discuss this issue are likely referring to what is sometimes called 'natural keys' versus a key based on AUTOINCREMENTed numbers which have no direct correspondence to the object. Some entities have the property of

Re: [sqlite] problem using random() in queries

2008-08-29 Thread cmartin
mySQL differs from MS SQL in it random function handling: SELECT i, RAND() AS R1, RAND() AS R2 FROM z where RAND() < .4; iR1R2 1 0.531666 0.692986 3 0.743755 0.906643 4 0.789811 0.04321 6 0.977431 0.576784 8 0.284047 0.336876 Different values for R1 and R2 (each

Re: [sqlite] If row exists use UPDATE else INSERT

2008-08-15 Thread cmartin
On Fri, 15 Aug 2008, Javier Julio wrote: > Is it possible in SQLite to have a single statement that basically > says if this row exists run an UPDATE statement, if not run an INSERT? You can INSERT rows that don't already exist. For example, the following creates 2 tables, FOO and BAR that

Re: [sqlite] Loading a existing database 100% into memory

2008-08-14 Thread cmartin
On Thu, 14 Aug 2008, D. Richard Hipp wrote: > On Aug 14, 2008, at 4:18 PM, Brown, Daniel wrote: > >> Hello Stefan, >> >> I'm trying to use the code snippet you suggested but when I try to >> query >> the master table of the attached database I get and error with the >> following message: >>

Re: [sqlite] multiple inserts

2008-08-11 Thread cmartin
On Mon, 11 Aug 2008, P Kishor wrote: On 8/11/08, Kodok M�rton <[EMAIL PROTECTED]> wrote: Hi, Does SQLite accepts multiple insert? insert into table (col1,col2) values (val1,val2), (val3,val4), (val5,val6) If not, how can I speed up large inserts? eg: 1000 rows one word... transactions

Re: [sqlite] sqlite3 basics

2008-07-22 Thread cmartin
On Tue, 22 Jul 2008, Keith Goodman wrote: > On Tue, Jul 22, 2008 at 7:27 AM, Milton Centeno <[EMAIL PROTECTED]> wrote: >> Thank you for your response. >> >> at the sqlite> prompt I enter sqlite3 test.db then I get ...> >> entering .databases gives me another ...> >> If I hit the Ctrl C it exits

Re: [sqlite] View with Dynamic Fields ?

2008-07-08 Thread cmartin
This gives the same result set you got and does not need to be edited: select applicantid, group_concat(answer, '|') from (select applicantid, answer from tblanswers order by questionid) group by applicantid; The group_concat() function is part of recent versions of SQLite. It

Re: [sqlite] Index and ORDER BY

2008-07-01 Thread cmartin
On Tue, 1 Jul 2008, Alexey Pechnikov wrote: > Is any difference between "CREATE INDEX ev_idx ON events(type,eid)" > and "CREATE INDEX ev_idx ON events(type,eid desc)"? What is "desc" keyword > for index? The DESC keyword creates the index in descending collation order, rather than ascending

Re: [sqlite] splitting field data

2008-06-18 Thread cmartin
On Wed, 18 Jun 2008, P Kishor wrote: >> CREATE TABLE foo (ID INTEGER PRIMARY KEY, ITEMS); >> INSERT INTO foo VALUES(1,item1;item2;item3); >> INSERT INTO foo VALUES(2,item1;item4); >> INSERT INTO foo VALUES(3,item5;item3;item7); > > The above is incorrect SQL. If you run the above INSERT

Re: [sqlite] splitting field data

2008-06-18 Thread cmartin
On Tue, 17 Jun 2008, Rich Shepard wrote: > You ought to normalize your data. Consider (and this is just off the top > of my head) two tables: one to hold categories and their descriptions, the > other to hold categories and items. Then you can select either specific > items, all items, or

[sqlite] splitting field data

2008-06-17 Thread cmartin
I have a table that contains 2 fields: ID, ITEMS, as: CREATE TABLE foo (ID INTEGER PRIMARY KEY, ITEMS); INSERT INTO foo VALUES(1,item1;item2;item3); INSERT INTO foo VALUES(2,item1;item4); INSERT INTO foo VALUES(3,item5;item3;item7); As shown, ITEMS is comprised of individual data items separated

[sqlite] delimiting text with embedded quotes

2008-05-08 Thread cmartin
I am converting text data from another database into SQLite. Some text data has embedded apostrophes like this: This was George's big day Other data has embedded double quotes like this: The box is 3" wide I am generating INSERT INTO statements for thousands of records to be used in

Re: [sqlite] SQLITE3 datatype

2008-05-07 Thread cmartin
On Wed, 7 May 2008, Joanne Pham wrote: I was wondering what is the different in size between int(8) and INTEGER datatype. Thanks, In the sense you are asking, there is no difference, but more importantly, the question reflects a misunderstanding of how SQLite is designed. Most database