Re: [sqlite] Compound update isn't working as I expect

2011-07-03 Thread Kurt Welgehausen
Sam Carleton wrote: > It is very clear to me that my expectations are wrong, please enlighten > me... > > Here is the query: > > update INVOICE >set SUB_TOTAL = (select sum(PRICE * QTY) from INVOICE_ITEM ii where > ii.INVOICE_ID = *INVOICE_ID*), >TAX =

Re: [sqlite] sqlite too slow for me?

2006-06-16 Thread Kurt Welgehausen
"P?ter Szab?" <[EMAIL PROTECTED]> wrote: > Dear SQLite Developers, > > I am seeking help for optimizing my SQLite SQL query, which seems to > be running unreasonably slow. The query is: > > SELECT col2 FROM t WHERE col1='foobar' AND > col4='foobarfoobarfoobarfoob'; > > My schema is the

Re: [sqlite] Avoiding duplicate record insertion

2006-06-18 Thread Kurt Welgehausen
[EMAIL PROTECTED] wrote: > I have a related question. Suppose I have a table containing rows each with > values and a counter. If the new row is unique in the table it should be > INSERTed and the counter set to 1; otherwise the counter for the matching > row should be incremented. It's a

Re: [sqlite] "NOT IN (Select..)" vs "EXCEPT Select"

2006-06-18 Thread Kurt Welgehausen
"Alexey Petrovsky" <[EMAIL PROTECTED]> wrote: > select e164 from lcr15 except select e164 from lcr16 order by ... > > select e164 from lcr15 where e164 not in (select e164 from lcr16) ... > > ... > I thought that those queries provide the same result. > ... > Any guess? > > Alexey. It's not

Re: [sqlite] Functions embedded in SQL statements

2006-06-18 Thread Kurt Welgehausen
Bud Beacham <[EMAIL PROTECTED]> wrote: > I have the book SQLite by Chris Newman and it has examples of > functions embedded within SQL statements. > > For example on page 38 the ifnull() function is used. > > SELECT code, ifnull(due_date, 'Ongoing') FROM projects; > > Also, on page 81 the

Re: [sqlite] Improving insert speed?

2006-06-24 Thread Kurt Welgehausen
> ... should I be using commit before end ... Commit and end are synonyms; you don't need both. > ... this is single user, I assume using immediate or exclusive is ok ... It's OK but not necessary. A simple begin is just as good, since no one else can apply a lock before yours is upgraded.

Re: [sqlite] How to get column description from table using SQL

2006-06-28 Thread Kurt Welgehausen
blins <[EMAIL PROTECTED]> wrote: > How to get column description from table using SQL pragma table_info() Regards

Re: [sqlite] Problem with WHEN expressions inside CASE statement in SELECT statement

2006-06-30 Thread Kurt Welgehausen
Robert Duff <[EMAIL PROTECTED]> wrote: > I had a problem with inserting bad unicode characters into my database. > ... > Returned by "SELECT locationsFK,variable,logindex, CASE units WHEN units > ISNULL THEN '#!#!#!#!#!#!#!#!#!#!' WHEN units = '??F' THEN > '' WHEN units !=

Re: [sqlite] Re: Multiple SELECTs (and single SELECT) and TRANSACTION ?

2006-08-08 Thread Kurt Welgehausen
"Igor Tandetnik" <[EMAIL PROTECTED]> wrote: > RohitPatel > wrote: > > Scenario 1 > > If action of some user needs to execute multiple SELECT statements > > (read-only, no plan to write), it needs to start explicit transaction > > to get consistent reads across read-only multiple statements.

Re: [sqlite] Seems like a bug in the parser

2006-08-23 Thread Kurt Welgehausen
> select a from qqq group by b; This question was discussed on the list a year or 2 ago. The column a in the simple query above is meaningless; it's an arbitrary value from each group. There are queries, however, where a non-grouped column is meaningful, such as a join where the grouping column

Re: [sqlite] duplicate primary key

2006-08-25 Thread Kurt Welgehausen
Mario Frasca <[EMAIL PROTECTED]> wrote: > this is a bit surprising; where does the autoincrement-like > behaviour come from? without an explicit autoincrement > definition, I would expect ... > what is going on here? I'm having three records with

Re: [sqlite] Performance Question

2006-08-28 Thread Kurt Welgehausen
[EMAIL PROTECTED] wrote: > Saying NOT NULL on a PRIMARY KEY is redundant, by the way. > -- > D. Richard Hipp <[EMAIL PROTECTED]> **kaw<~/tdpsa>$ sqlite3 Loading resources from /home/kaw/.sqliterc SQLite version 3.3.7 Enter ".help" for instructions sqlite> .nullvalue '<>' sqlite> create table

Re: [sqlite] nested functions in select

2005-05-13 Thread Kurt Welgehausen
> ... select count(distinct(something)) ... http://www.sqlite.org/omitted.html BTW, distinct is not a function. Regards

Re: [sqlite] unit tests

2005-05-22 Thread Kurt Welgehausen
> How do I run the unit tests in Linux? make test

Re: [sqlite] philosophy behind public domain?

2005-05-25 Thread Kurt Welgehausen
> > > I think the gist was that the software couldn't have a disclaimer > > > of liability if it is public domain, and so anyone could sue the > > > author if something went wrong when using it. I don't know how > > > true this is or not, but would like to see it addressed in the answer. >

Re: [sqlite] Re: 'int n' parameter in sqlite3_bind_*() functions

2005-06-04 Thread Kurt Welgehausen

Re: [sqlite] Parsing bug with sqlite3.exe ?

2005-06-22 Thread Kurt Welgehausen
The problem seems to be that sqlite is not recognizing as a comment terminator (unless the comment is alone on a line). I think you should write a bug ticket; meanwhile, the work-around is to end your comment with a semicolon. sqlite> select * from t1; -- comment ...> ...> ; a c

Re: [sqlite] how to use the LIMIT and OFFSET?

2005-07-02 Thread Kurt Welgehausen
You should use SQL to get the data you want, then use your host language to display the data. Those are 2 separate operations, and you'll be better off not mixing them. If you're going to let your users go backward, you're going to have to cache the data anyway. Regards

Re: [sqlite] Does SQLite have a fulltext search like MySQL?

2005-07-05 Thread Kurt Welgehausen
> From: Stephen Leaf <[EMAIL PROTECTED]> > Organization: SMiLeaf > To: sqlite-users@sqlite.org > Date: Tue, 5 Jul 2005 18:06:39 -0500 > Subject: Re: [sqlite] Does SQLite have a fulltext search like MySQL? > > On Tuesday 05 July 2005 04:48 pm, Michael Grice wrote: > > If not, are there plans to add

Re: [sqlite] Saving "Unsigned Long"s in database

2005-07-12 Thread Kurt Welgehausen
> SELECT avg(length(domain)) from domains; > > The response is 10 and not 4. >From : length(X) Return the string length of X in characters. If SQLite is configured to support UTF-8, then the number of UTF-8 characters is

Re: [sqlite] Calculation between rows?

2005-07-18 Thread Kurt Welgehausen
> I suppose I could copy the data table into a temporary > table ... documenataion quarantees AUTOINCREMENT fields > to be "monotonically increasing" ... "Monotonically increasing" does not mean that for every key k there will be a key (k-1). In fact, if you ever delete a row in an autoincrement

Re: [sqlite] Limit how much data to read

2005-07-25 Thread Kurt Welgehausen
> Where in the documentation that explains how to use the sqlite substr() > function? Go to the SQLite Syntax page, and click on 'expression'.

Re: [sqlite] SQLite 3.2 install on Mac OS X

2005-07-26 Thread Kurt Welgehausen
cd /usr/bin ln -s tclsh8.4.4 tclsh will probably do it for you, or to cover all the bases, cd /usr/bin ln -s tclsh8.4.4 tclsh8.4 ln -s tclsh8.4 tclsh Regards

Re: [sqlite] SQLite 3.2 install on Mac OS X

2005-07-27 Thread Kurt Welgehausen
It looks like libtclsqlite3.dylib either didn't get made or is in the wrong place. You can search your disk to see whether it got made. There's a switch in the Makefile that determines whether it gets made or not. OS X has been discussed several times on this list. You might try searching the

Re: [sqlite] ALTER TABLE: Confusing documentation

2005-07-27 Thread Kurt Welgehausen
The docs are correct; you just have to read carefully. They say that you can "rename, or add a new column to, an existing table". Regards

Re: [sqlite] quoting

2005-07-31 Thread Kurt Welgehausen
> How do I quote a null byte? There is no way to quote a null byte in SQL. If you're trying to pass a string with a literal null byte in it to a perl function, it may be that underlying C code is failing because it expects a null-terminated string; in that case, substituting "\000" for the null

Re: [sqlite] how to get notice when database is free after it was busy

2005-08-05 Thread Kurt Welgehausen
> So, you can use something like this: > > if { ! [catch {set selectResult [eval db "select x from y where z = > 'blah'"]} catchErr] } { > puts "Failed - the error is $catchErr" > } else { > puts "selectResult= $selectResult" > } > Except that you've got the if/else clauses reversed (or just

Re: [sqlite] RFC Beginning digit in column name

2005-08-09 Thread Kurt Welgehausen
> Does MySQL allows [0_xy] to specify column names? > > If I remember correctly, that is the standard SQL way to have columns > with spaces in the middle, and sqlite allows columns starting with a > digit in this way... It's not standard; it's a Microsoft extension. Regards

Re: [sqlite] Finding max of each group?

2005-08-10 Thread Kurt Welgehausen
> select conversation_id, count(*), max(unread), max(updated_on) > from messages > where conversation_id in () > group by conversation_id; > > I use max(updated_on) to get the date of the most recent message in > the conversation. Is there a way to return the ID of this message? Assuming that the

Re: [sqlite] possible documentation error

2005-08-10 Thread Kurt Welgehausen
AS is always optional, i.e., in table names and in column names. Regards

Re: [sqlite] Index and General Optimization Question

2005-08-12 Thread Kurt Welgehausen
Currently, indices are not used to look up either term in an OR expression. See . You could build 2 separate 5-column indices and use a union. Regards

Re: [sqlite] How to update wiki? date/time page misprint

2005-08-16 Thread Kurt Welgehausen
> strftime ... for the %j format ... should specify > 001-366 instead of 000-366 You're correct. You should be able to edit the page by clicking on Edit just below the main heading. Regards

Re: [sqlite] manipulating new.? in triggers

2005-08-18 Thread Kurt Welgehausen
> Is it possible to change the values of certain rows that > are inserted into the database? ... I think everything you need is explained at . If you don't understand how to get the current date in SQLite, look at the wiki page. Regards

Re: [sqlite] blocking - busy_timeout vs database is locked(5)

2005-08-19 Thread Kurt Welgehausen
> Using ... Tcl ... how do I even obtain the value ... man n catch > If I use db timeout 2000 ... SQLite will retry for 2 seconds, then if the db is still locked, it should return an error code. Any SQLite command that can fail should be run within a catch command. Regards

Re: [sqlite] SQL for finding non-matching rows

2005-08-19 Thread Kurt Welgehausen
> select a from t1 where a not in (select b from t2) select a from t1 except select b from t2 or (SQLite v3 only) select a from t1 where not exists (select b from t2 where b = a) Which of these is fastest will probably depend on table size and indexing; you'll have to try them out.

Re: [sqlite] malformed database schema - near "AUTOINCREMENT": syntax error

2005-08-23 Thread Kurt Welgehausen
> "malformed database schema - near "AUTOINCREMENT": syntax error" It might help if you published your schema. Also, it would be interesting to know whether your v3.2.3 passed all the tests in the test suite. Regards

Re: [sqlite] "where not exists (union-select)" fails on 2nd where

2005-08-23 Thread Kurt Welgehausen
> SELECT * FROM PRIM AS P > WHERE NOT EXISTS > ( > SELECT REFID FROM REF1 WHERE REF1.REFID=P.ID > UNION > SELECT REFID FROM REF2 WHERE REF2.REFID=P.ID > ); I looks like correct SQL according to the SQLite docs, but I don't understand why you coded the select that way. You should get the same

Re: [sqlite] unixepoch seconds issue?

2005-08-31 Thread Kurt Welgehausen
> 2005-08-30 15:19:00 > 2005-08-30 08:19:00 Your time zone is -7. Read the SQLite docs to see how to convert between local time and UTC (they're in the wiki). Regards

Re: [sqlite] Null values in Primary Key

2005-09-05 Thread Kurt Welgehausen
> I tried to see what MS SQL would do but it does not allow creation of the > tables primary key with any NULL segments. This is the correct behavior; I thought SQLite enforced that. You can duplicate the correct behavior by adding to the spec for each PK column. > Does that mean I need to do

Re: [sqlite] how to check the file is a sqlite database file?

2005-09-05 Thread Kurt Welgehausen
Read the first 31 bytes of the file (or the first 15 bytes in V3). Regards

Re: [sqlite] All transactions are Database locks?

2005-09-07 Thread Kurt Welgehausen
> Are all transactions database locks (not table locks)? Yes. > If I prepare a select statement and start stepping on Table A on process one > and pause before finializing and do an update on Table B with process two or > even the same process I will get a database is locked error? Yes. > Does

Re: [sqlite] any plans for out-of-row blobs?

2005-09-13 Thread Kurt Welgehausen
> There is no way to retrieve part of a > blob value in SQLite. > > From my understanding, most databases store > blob values separate from the rest of the row. The common way to handle this in SQLite is to store the blob in a file and store the file name in the db. Regards

Re: [sqlite] How to load a SQLite schema from file using SQLite C-API

2005-09-16 Thread Kurt Welgehausen
>I have SQLite schema in an ascii file. I would like to be able to load >this schema via SQLite C-API. How do I do this? You can look at the source code for the SQLite shell and see how it implements the .read command, but it may be simpler just to invoke the SQLite shell using system() or

Re: [sqlite] sqlite on embedded board

2005-09-16 Thread Kurt Welgehausen
I can't tell what you've been doing, but probably what you need to do is something like 1. Do all your work in the memory db, without touching the flash db. 2. Then, just before you detach, update the flash db with insert [or replace] into flash.db ... select ... from

Re: [sqlite] INSERTing a row only if it doesn't EXIST

2005-09-17 Thread Kurt Welgehausen
If id in your example identifies a row, then by definition it is unique (probably the primary key). If you try to insert another row with the same id, the insert will fail. Why not just catch the exception or error code? Regards

Re: [sqlite] index organized table +compression

2005-09-18 Thread Kurt Welgehausen
> Does anybody know whether it is possible to compress ... I don't recall anyone having claimed to have done this. You can try searching the archives: > ... possible to create index organized tables within SQLite Not really. If you

Re: [sqlite] help with GROUP BY

2005-09-19 Thread Kurt Welgehausen
> I want the title, the MAX(speed) for each title group, and the date > that occurred. In case, ... I haven't tried this, but I think it's correct. Let me know if it's not. select distinct table.title, table.date, t2.maxsp from table, (select title ttl, max(speed) maxsp from table

Re: [sqlite] "Data Dictionary" in SQLite?

2005-09-21 Thread Kurt Welgehausen
pragma table_info (and others). Look at the pragma docs. Regards

Re: [sqlite] Easyer way ?

2005-09-24 Thread Kurt Welgehausen
I believe more than one subscriber to this list have written utilities to manage SQLite databases. I've never used one, but I expect they have GUIs that will allow you to enter and insert data. A Google search will probably turn up something. Regards

Re: [sqlite] locking problem (on insert inside callback)

2005-10-14 Thread Kurt Welgehausen
> SELECT * FROM table1 LEFT JOIN table2 on table1.field=table2.field > WHERE table2.field is NULL > > So far, so good, I get the records I want. Then in the callback, I try > > INSERT INTO table1 etc... I'm not sure I understand your logic. Your left join indicates that there are records missing

Re: AW: [sqlite] and , or

2005-10-18 Thread Kurt Welgehausen
> Hi Henri, > > On 18.10.2005, at 14:56, Eggert, Henri wrote: > > > I have found that the problem is the column name "Text". > > If I replace the column name "Text" by another all works fine. > > So I wonder : is "Text" a keyword for sqlite ? > > > > It indeed is [1]. :) > > > -Markus > > [1]

Re: [sqlite] How to determine if a column is autoincremented?

2005-10-22 Thread Kurt Welgehausen
select sql from sqlite_master where type='table' and tbl_name='my_table' and sql like '%autoincrement%' OR select sql like '%autoincrement%' from (select sql from sqlite_master where type='table' and tbl_name='my_table') Regards

Re: [sqlite] .import null values

2005-10-22 Thread Kurt Welgehausen
If you want all the empty fields to be null, why not just set them, with a single sql statement (per affected column), after the import? Regards

Re: [sqlite] sqlite on SunOS 5.6 (Solaris Sparc 6)

2005-10-25 Thread Kurt Welgehausen
This looks like a problem that has come up several times before on the list. If I remember correctly, it's usually that the awk shipped by Sun doesn't support the extended syntax that Sqlite expects. Try installing gawk if it's not there already, and do whatever is required to use it in place of

Re: [sqlite] .import null values

2005-10-25 Thread Kurt Welgehausen
> "even numeric data" is imported as text Column types in Sqlite are not strictly enforced. If you import data that 'look' numeric from your text file into a column with a numeric type, the data will be stored in a numeric format; if a particular value cannot be converted to a numeric format, it

Re: [sqlite] Are DELETE TRIGGERS recursive or not?

2005-10-25 Thread Kurt Welgehausen
If the trigger were recursive, it would invoke itself forever, even if it's not doing anything because the where clause fails. Regards

Re: [sqlite] sqlite on SunOS 5.6 (Solaris Sparc 6)

2005-10-25 Thread Kurt Welgehausen
> Can anyone guide me to where "fdatasync" should be? (maybe) Regards

Re: [sqlite] Is this query optimized?

2005-10-25 Thread Kurt Welgehausen
count() has always done a full table scan. As far as I know, nothing has been done to optimize it, as your observations seem to confirm. Regards

Re: [sqlite] .import null values

2005-10-25 Thread Kurt Welgehausen
> Affinity Modes ... how do you activate these? I don't believe strict affinity has been implemented; someone correct me if that's not right. Regards

Re: [sqlite] Unsupported SQL feature

2005-11-06 Thread Kurt Welgehausen
> What are these constructs suppose to do? x all (select y from t where ...) is equivalent to not exists (select y from t where not (x y) and ...) Any and some are synonyms. x any (select y from t where ...) is equivalent to exists (select y from t where x y and ...) Any can

Re: [sqlite] Re: Calculating the mode

2005-11-21 Thread Kurt Welgehausen
> select salary, count(*) occurs from payroll > group by salary having occurs = > (select count(*) c from payroll group by salary >order by c desc limit) OR select salary, count(*) from payroll group by salary having count(*) = (select max(cnt) from (select count(*) cnt

Re: [sqlite] Re: - [sqlite] can anyone reproduce ticket# 1540 (failure to create a primary key)?

2005-11-29 Thread Kurt Welgehausen
> ... and then query the sqlite_master for the > names of other indexes ... Or use PRAGMA index_list(table-name) and PRAGMA index_info(index-name) Regards

Re: [sqlite] Odd query optimization

2005-12-02 Thread Kurt Welgehausen
> Anyone have any thoughts ... ? You've already figured it out. For more info, see . Regards

Re: [sqlite] specifying field type, any benefit?

2006-01-04 Thread Kurt Welgehausen
http://www.sqlite.org/datatype3.html

Re: [sqlite] enum in SQLite

2006-01-05 Thread Kurt Welgehausen
> SQLite doesn't support enums natively. You could emulate it using > triggers, although it would be somewhat hidden and definitely a pain in > the tucus to use. It's not really so hard. create table MainTbl ( ... EnumCol SomeType references EnumVals, ...); create table EnumVals (val

Re: [sqlite] enum in SQLite

2006-01-05 Thread Kurt Welgehausen
> ... you'll also need to write an update trigger ... True, and you may want to protect EnumVals with triggers after you populate it, or put EnumVals is a separate read-only database and attach it. On the other hand, being able to change the allowed values without changing the schema may be an

Re: [sqlite] translate time comparison statement

2006-01-11 Thread Kurt Welgehausen
You may want WHERE julianday(date('now')) - julianday(date(arrival_date)) > 7 so that time of day isn't part of the comparison; otherwise, you're correct. Regards

Re: [sqlite] PRAGMA table_info oddness

2006-01-22 Thread Kurt Welgehausen
There's no string type in SQL. Go to and read section 2.1. Regards

Re: [sqlite] GUID in place of AUTO_INCREMENT

2006-01-25 Thread Kurt Welgehausen
> ... but Rails doesn't seem to support composite keys. I don't know much about RoR. How does RoR keep you from creating a multi-column key on an SQLite table? Regards

Re: [sqlite] self joins efficiency question

2006-02-02 Thread Kurt Welgehausen
> select user_id from person p1, person p2 where p1.user_id<>p2.user_id > and p1.name = p2.name Your query is fine. It's slow because it's doing a full scan of p1, and for each row in p1 it's doing a full scan of p2. That makes the time O(n^2). It should go much faster if you add an index on

Re: [sqlite] examples of syntax

2006-02-05 Thread Kurt Welgehausen
> examples of syntax ... than the "syntax" page from the home page Try Googling for 'sql tutorial'. Regards

Re: [sqlite] alter table syntax ?

2006-02-23 Thread Kurt Welgehausen
"Doug Fajardo" <[EMAIL PROTECTED]> wrote: > Help! > I keep getting a syntax error from the 'alter table' sql command, when > used to add a column to a table. Can someone help with this error? Below > is an example of one attempt, and its results: > > [tuna]$ sqlite test2.db > SQLite version

Re: [sqlite] DEFAULT values replace explicit NULLs?

2006-03-04 Thread Kurt Welgehausen
The current implementation is correct. According to the '92 std (remembering that the default default value of a column is null) the procedure for constructing a new row is (conceptually) 1. Construct a row containing the default value for each column. 2. For each column value specified

Re: [sqlite] Wierd between results

2006-03-25 Thread Kurt Welgehausen
"Lloyd Thomas" <[EMAIL PROTECTED]> wrote: > if use > SELECT count(call_id) as num_rows WHERE ring_time BETWEEN 6 and 10; > I get 0 results You DID specify a table in your actual queries, didn't you? select * from tbl; t x y -- -- -- 1

Re: [sqlite] select foo,count(foo) -> segmentation fault

2006-03-28 Thread Kurt Welgehausen
"Jay Sprenkle" <[EMAIL PROTECTED]> wrote: > > Works ok here: > > C:\Temp\sqliteImport>sqlite3 > SQLite version 3.0.8 > Enter ".help" for instructions > sqlite> create table t (a); > sqlite> select * from (select count(a) as b from t) where b > 1; > sqlite> select count(a) as b from t where b > 1;

Re: [sqlite] last modified time or version of sqlite database

2006-04-03 Thread Kurt Welgehausen
Regards

Re: [sqlite] how to fix problem of lock

2006-04-03 Thread Kurt Welgehausen
"Cesar David Rodas Maldonado" <[EMAIL PROTECTED]> wrote: > but is posible open several DATABASE with a programm and do transaccion > without locked the table??? No. A transaction will always lock the database at some point. Regards

Re: [sqlite] Does Substr() use index?

2006-04-16 Thread Kurt Welgehausen
Paul Gaspar <[EMAIL PROTECTED]> wrote: > Hello, just a short question: Does this use the index on f > > select * from t where SUBSTR(f,1,1) = 'a' > > so that it is an alternative for > > select * from t where ( f >= 'a' and f < 'b' ) > > > Thanks a lot > > Paul No, but you can read about

Re: [sqlite] sqlite puzzle

2006-05-02 Thread Kurt Welgehausen
JP <[EMAIL PROTECTED]> wrote: > SQLite provides a way to get the N-th row given a SQL statement, with > LIMIT 1 and OFFSET . > > Can the reverse be done in an efficient way? For example, given a table > with 1million names, how can I return the row number for a particular > element? No, you

Re: [sqlite] How do you unsubscribe?

2006-06-01 Thread Kurt Welgehausen
Richard Battagline <[EMAIL PROTECTED]> wrote: > How do you unsubscribe? Read the headers of any message from the group.

Re: [sqlite] Binary compatibility

2006-06-01 Thread Kurt Welgehausen
Nikki Locke <[EMAIL PROTECTED]> wrote: > > Given that I am using SQLite.Net, if I execute this pragma as the first > command > passed over the connection, before creating any tables, will it work? Or do I > really have to execute it "before creating the database"? I think you're confusing

Re: [sqlite] Query performance issues - index selection

2006-06-01 Thread Kurt Welgehausen
Steve Green <[EMAIL PROTECTED]> wrote: > CREATE UNIQUE INDEX pk_data on data( utime, r_id, u_id ); > ... > CREATE INDEX ix_data_ut on data( u_id, utime ); Wouldn't a unique index on (u_id, utime, r_id) get you the pk constraint and better performance on your query with no other index to confuse

Re: [sqlite] updating using a value from another table

2013-10-07 Thread Kurt Welgehausen
Simon Slavin wrote: > > On 7 Oct 2013, at 3:45pm, dean gwilliam wrote: > > > sqlite> UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE > > raw_nm = x > > .raw_nm); > > looking at it without the 'AS' ... > > UPDATE itms SET

Re: [sqlite] Modifying an existing table

2003-10-30 Thread Kurt Welgehausen
>> From: "Dennis Volodomanov" <[EMAIL PROTECTED]> >> Date: Thu, 30 Oct 2003 22:04:18 +1100 >> Subject: [sqlite] Modifying an existing table >> >> Hello everybody, >> >> Is there any easy way to modify an existing table? I need to do three things: >> >> 1) add one more column >> 2) change the name

Re: [sqlite] UPDATE with result from subquery

2003-10-30 Thread Kurt Welgehausen
>> Date: Thu, 30 Oct 2003 12:45:17 +0100 >> From: Bart Duchesne <[EMAIL PROTECTED]> >> >> update mail set size = (select m1.size from mail m1 where m1.id = id and >> m1.sender is not null) where sender is null; Your statement doesn't work for at least 2 reasons. (1) (m1.id = id) is always true

Re: [sqlite] thread failed to start - need a CLONE method added (SQLite and Perl threads)

2003-11-23 Thread Kurt Welgehausen
Have you read the article on threads in the FAQ at ? - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] User Functions

2003-12-05 Thread Kurt Welgehausen
There's no good way to handle graphs in SQL. If you had a tree, you could use Celko's nested-set scheme, but from your description, you don't have a tree (unless you're looking at it upside-down). Celko's book, _SQL for Smarties_, has a short chapter on handling non-tree graphs. Also there's a

Re: [sqlite] autoincrement ... primary-key bug

2003-12-08 Thread Kurt Welgehausen
> CREATE TABLE User ( Name VARCHAR (40), > UID INTEGER, > DeviceID VARCHAR (64) DEFAULT 'Unknown', > PRIMARY KEY (Name, UID, DeviceID) ); > INSERT INTO User (Name,DeviceID) VALUES

[sqlite] List management / auto replies

2003-12-26 Thread Kurt Welgehausen
I submitted a msg to the group this morning and got an automatic reply directly from (apparently) a list subscriber who is on vacation. For the next 10 days, everyone who posts to the group is going to get an auto-reply from this fellow. Can the list software do anything to stop this?

Re: [sqlite] Column names without the callback function

2003-12-28 Thread Kurt Welgehausen
- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] How to find the primary key ?

2003-12-28 Thread Kurt Welgehausen
You can try splitting the create-table statement on the regular expression [,()]|\s+. (The . is not part of the RE.) Then you need to look at the tokens that match the RE and at the strings between those tokens. With this method, I needed about 400 lines of tcl to do a complete parse with error

Re: [sqlite] How to find the primary key ?

2003-12-30 Thread Kurt Welgehausen
>> Hi Kurt. I could use code to parse out the other stuff too. >> If its written in C/C++, would you be willing to share it? >> >> cheers >> -brett It's a tcl function that returns a list containing a string, 5 lists of strings, and 3 lists of lists of strings. You'd have to translate it. It's

Re: [sqlite] UNIQUE vs PRIMARY KEY

2003-12-30 Thread Kurt Welgehausen
>> ... are UNIQUE columns basically the same as PRIMARY KEY ...? No, they're not the same thing. There are dozens of elementary articles on database theory on the www. Try a Google search on 'database primary key' or something like that.

[sqlite] tcl array for query results

2004-01-01 Thread Kurt Welgehausen
I've just discovered (perhaps everyone else already knew) that tcl arrays used to hold query results are not cleared before they're used. If the same array is used repeatedly, it can get quite large and affect execution time. More details are at .

Re: [sqlite] Documentation

2004-01-05 Thread Kurt Welgehausen
- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Date and Time functions are experimental

2004-01-05 Thread Kurt Welgehausen
DRH wrote >> The date and time functions ... are fully tested ... I'm using SQLite version 2.8.8, precompiled binaries, on Linux. The date/time functions seem to work, but some of the modifiers don't. Specifically, 'gregorian', 'julian', 'start of week', 'localtime', and 'utc' all cause the

Re: [sqlite] Update using multiple tables

2004-01-06 Thread Kurt Welgehausen
Look at the docs for INSERT and consider using INSERT OR REPLACE with a SELECT clause. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] How to update a whole column

2004-01-10 Thread Kurt Welgehausen
What you propose will work, but you haven't given enough info for anyone to tell you whether it's the best solution. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Triggers

2004-01-10 Thread Kurt Welgehausen
Try create trigger itemDelTrg after delete on Items for each row begin delete from Stats where Stats.ID = old.ID; end; Also consider create trigger statInsTrg before insert on Stats for each row when (select count(ID) from Items where ID = new.ID) = 0 begin select

Re: [sqlite] manual? documentation?

2004-01-14 Thread Kurt Welgehausen
If you haven't already, go to the bottom of the main page at sqlite.org and click on the link to the wiki; then click on contents. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]

  1   2   3   >