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

[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 std_nm=(SELECT std_nm FROM aliases WHERE raw_nm = 
> aliases.raw_nm);
>
> I'm wondering whether you actually mean
>
> UPDATE itms SET std_nm=(SELECT std_nm FROM aliases WHERE raw_nm = 
> itms.raw_nm);
>
> or something like that.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

sqlite> create table t (k integer primary key, d integer);
sqlite> insert into t (k) values (1);
sqlite> insert into t (k) values (2);
sqlite> insert into t (k) values (3);
sqlite> create table t2 (k integer primary key, d integer);
sqlite> insert into t2 (d) values (101);
sqlite> insert into t2 (d) values (102);
sqlite> select * from t;
k   d 
--  --
1   <>
2   <>
3   <>
sqlite> select * from t2;
k   d 
--  --
1   101   
2   102   
sqlite> update t set d = (select d from t2 where t2.k = t.k);
sqlite> select changes();
changes() 
--
3 
sqlite> select * from t;
k   d 
--  --
1   101   
2   102   
3   <>
sqlite> update t set d = null;
sqlite> update t set d = 103 where k = 3;
sqlite> select * from t;
k   d 
--  --
1   <>
2   <>
3   103   
sqlite> update t set d = (select d from t2 where t2.k = t.k);
sqlite> select changes();
changes() 
--
3 
sqlite> select * from t;
k   d 
--  --
1   101   
2   102   
3   <>
sqlite> update t set d = null;
sqlite> update t set d = 103 where k = 3;
sqlite> select * from t;
k   d 
--  --
1   <>
2   <>
3   103   
sqlite> update t set d = (select d from t2 where t2.k = t.k) 
   ...> where t.k in (select k from t2);
sqlite> select changes();
changes() 
--
2 
sqlite> select * from t;
k   d 
--  --
1   101   
2   102   
3   103   


In the original post, the columns raw_nm and x.raw_nm
are the same column, so the condition is always true.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 = (select round( sum(PRICE * QTY) * ( 8.25 / 100) + .005, 2) from
> INVOICE_ITEM ii where ii.INVOICE_ID = *INVOICE_ID*),
>TOTAL = (select round( sum(PRICE * QTY) * ( 1 + ( 8.25 / 100)) +
> .005, 2) from INVOICE_ITEM ii where ii.INVOICE_ID = *INVOICE_ID*),
>updatedby =  'manual2';
>
> The result is that every SUB_TOTAL, TAX, and TOTAL are very large, I assume
> it is the grand total.  It was my impression that the INVOICE_ID on the
> right of each where clause would be the current invoice_id of the given row
> being updated.  Am I wrong?  If so, is there some way to do this update in
> one update statement?
>
> Sam
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance Question

[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 t (k int primary key, d char);
sqlite> insert into t (k, d) values (1, 'abc');
sqlite> insert into t (k, d) values (1, 'def');
SQL error: column k is not unique
sqlite> insert into t (k, d) values (null, 'ghi');
sqlite> insert into t (k, d) values (null, 'jkl');
sqlite> select * from t;
k   d 
--  --
1   abc   
<>  ghi   
<>  jkl


Am I missing something, or should I write a bug ticket
about a primary key accepting nulls?

Regards

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] duplicate primary key

[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 the
> same NULL primary key.

   This is a violation of the '92 std. I believe SQLite used
   to enforce the non-nullability of primary keys. I recall
   that the question whether SQLite should enforce this
   constraint was discussed here, and I thought it was decided
   that it should.


Regards

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[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 is a foreign key that
references the primary key of another table.

I don't remember whether the '92 std calls this an error,
but later stds allow the dbms to analyze such functional
dependencies to decide whether to accept or reject a query
with a non-grouped column in the column list.  In SQLite
you have to do the functional-dependency analysis yourself.

Regards

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[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.
> >
> > Q1. Which one is preferable -> BEGIN TRANSACTION  or  BEGIN IMMEDIATE
> > ?
>
> BEGIN IMMEDIATE would block other readers. Don't use it unless you plan 
> to write.

BEGIN IMMEDIATE blocks writers, not readers.  I believe it's
what he wants.

Regards

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

[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 != '??F' THEN '!!!' ELSE 
> 'WHY DOES ONLY THE ELSE WORK???' END FROM data LIMIT 10;"
> "1", "NVE_AI_2Boiler2_SupplnvoValu", "5", "WHY DOES ONLY THE ELSE 
> WORK???",
> ...

I don't know if there's any other problem, but it looks like
you don't understand how case expressions work. There are
two forms, and you're confusing them. If you remove the
first instance of 'units' in your query, it may work as you
were expecting.

The SQLite documentation 
gives the correct grammar but doesn't explain the semantics.
You can probably find examples of the two variants in one of
the several introductions to SQL on the web.

Regards

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

[Kurt Welgehausen]

blins <[EMAIL PROTECTED]> wrote:

>   How to get column description from table using SQL

pragma table_info()




Regards

Re: [sqlite] Improving insert speed?

[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.

Regards

Re: [sqlite] Functions embedded in SQL statements

[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 strftime() function is used.
>
> SELECT strftime('%m/%d/%Y', '2004-10-31');
>
> The problem I am having is that I cannot find any documentation
> on the SQLite documentation page that explains these functions. 
> What other functions exist?  What is the syntax?  Where can they
> be used in SQL statements?  Is there any documentation on these
> functions?
>
> My apologies if I have overlooked it.
>
> Thanks.

The info you're looking for can all (probably) be reached
from . Start with the
'expression' section; then read the other sections to see
where expressions can be used.

Regards

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

[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 necessary to guess. The results are correct. The first
query produces a set difference; the second selects all rows
that satisfy the where clause; they are not the same.

An equivalent for your first query is

 select distinct e164 from lcr15 where e164 not in (select ...)


Regards

Re: [sqlite] Avoiding duplicate record insertion

[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 classic data reduction procedure.
>
> As most of the operations will be UPDATEs I want to do something like
>   UPDATE ... if failed INSERT...
> but I can't see an efficient way to express that in SQL understood by
> sqlite.
>
> Thanks,

Don't worry so much about efficiency until you're sure you
have a problem.

 insert or ignore into tbl (primaryKey, counter, otherVal, ...)
 values (thisPK, 0, thisOtherVal, ...);

 update tbl set counter = counter + 1 where primaryKey = thisPK;


Regards

Re: [sqlite] sqlite too slow for me?

[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 following:
>
>   CREATE TABLE t (
> col1 text NOT NULL,
> col2 integer NOT NULL,
> col3 integer NOT NULL,
> col4 text NOT NULL,
> col5 text NOT NULL,
> PRIMARY KEY(col1, col2, col3, col4, col5),
> UNIQUE(col1, col4, col5),
> UNIQUE(col4, col5, col1),
> UNIQUE(col5, col4, col1),
>   );

Just a guess: try

   SELECT +col2 FROM t ...


Regards

Re: [sqlite] Query performance issues - index selection

[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 things?

Regards

Re: [sqlite] Binary compatibility

[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 creating the database with creating the
file. When you open a non-existent db file, your file system is
probably creating a file of zero size; the database hasn't been
initialized yet. You can easily verify this by looking at the
file size after you open() the db but before you do anything else.

Regards

Re: [sqlite] How do you unsubscribe?

[Kurt Welgehausen]

Richard Battagline <[EMAIL PROTECTED]> wrote:

> How do you unsubscribe?

Read the headers of any message from the group.

Re: [sqlite] sqlite puzzle

[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 can't do that in SQL.  The results of an SQL query
are a set of rows; the rows are not produced in any
guaranteed order.  The set is converted to an ordered list
only by a sort (order by) after all the result rows are
retrieved.

If I understand correctly what you want to do, you'll have
to retrieve all the rows into an ordered collection, then
search for the name you're interested in.  An alternative
would be to add a column RowNum and update it every time
you insert a row.

  newRowNum <- 1 + {select max(RowNum) from t where Name < 'foo'} or 0
  update t set RowNum = RowNum + 1 where RowNum >= newRowNum
  insert into t (Name, RowNum, ...) values ('foo', newRowNum, ...)

Regards

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

[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 like and glob here:

  .

Regards

Re: [sqlite] how to fix problem of lock

[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] last modified time or version of sqlite database

[Kurt Welgehausen]

Regards

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

[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;
> 0
> sqlite>

No, it doesn't work ok.  Count(a) (aka b) is zero, so if
the where clause were applied there would be no result.
The problem is that it's not valid sql.  Version 3.3.3
throws an error (misuse of aggregate) instead of segfaulting.

Regards

Re: [sqlite] Wierd between results

[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   100 101   
2   100 102   
3   100 103   
4   100 103   
5   200 210   
6   200 220   
7   200 230   
8   300 199   

select count(t) from tbl where y=103; 
count(t)  
--
2 

select count(t) from tbl where y between 102 and 210; 
count(t)  
--
5 


Regards

Re: [sqlite] DEFAULT values replace explicit NULLs?

[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 in the values
 clause, replace the default value in the
 corresponding column of the new row with the
 specified value.  There is no exception for a
 specified value of null.

Regards

Re: [sqlite] alter table syntax ?

[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 2.8.16
> Enter ".help" for instructions
> sqlite> create table x1 ( name );
> sqlite> alter table x1 add column ( phone );
> SQL error: near "alter": syntax error
> sqlite>

There's no  statement in Sqlite v2.x;
switch to v3 if you need it.

Regards

Re: [sqlite] examples of syntax

[Kurt Welgehausen]

> examples of syntax ...  than the "syntax" page from the home page

Try Googling for 'sql tutorial'.

Regards

Re: [sqlite] self joins efficiency question

[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 name.
You may also need to reverse the order of the predicates
so SQLite will pick the right index -- the optimizer may
handle it correctly anyway, but I'm not sure.

Regards

Re: [sqlite] GUID in place of AUTO_INCREMENT

[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] PRAGMA table_info oddness

[Kurt Welgehausen]

There's no string type in SQL. Go to

and read section 2.1.

Regards

Re: [sqlite] translate time comparison statement

[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] enum in SQLite

[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
advantage.

Regards

Re: [sqlite] enum in SQLite

[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 SomeType);

Now you have to enforce the foreign key with a trigger.

 create trigger EnumTrg before insert on MainTbl for each row
 when (select count(*) from EnumVals where val = new.EnumCol) = 0 begin
   select raise(rollback, 'foreign-key violation: MainTbl.EnumCol');
 end;


Regards

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

[Kurt Welgehausen]

http://www.sqlite.org/datatype3.html

Re: [sqlite] Odd query optimization

[Kurt Welgehausen]

> Anyone have any thoughts ... ?

You've already figured it out. For more info, see
.

Regards

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

[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] Re: Calculating the mode

[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 from payroll group by salary))

Regards

Re: [sqlite] Unsupported SQL feature

[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 be confusing because of its ambiguity in the English
language. If I say 'Is x greater than any y in t?', almost
everyone will say the answer is yes if x is greater than one
or more y-values in t -- but if I say 'x is greater than any
y in t', you may interpret that to mean that x is greater
than every y-value in t. This has lead any and all to be
deprecated for new code in some circles.

Regards

Re: [sqlite] .import null values

[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] Is this query optimized?

[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] sqlite on SunOS 5.6 (Solaris Sparc 6)

[Kurt Welgehausen]

> Can anyone guide me to where "fdatasync" should be?



(maybe)


Regards

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

[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] .import null values

[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 will be stored as text -- so you may
have '' in a numeric column where you want a null.
See  for a more
rigorous explanation.

BTW, you shouldn't have to type individual update
statements for each column. Sqlite is usable from
almost all scripting languages. For example, in
tcl if you get the column names into a variable
called columns, you can do something like

  load tclsqlite3.so
  sqlite dbcmd filename.db
  dbcmd eval {begin exclusive}
  foreach c $columns {
dbcmd eval {upate table set $c=null where $c=''}
  }
  dbcmd eval commit
  dbcmd close


Regards

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

[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 awk.

Regards

Re: [sqlite] .import null values

[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] How to determine if a column is autoincremented?

[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: AW: [sqlite] and , or

[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] http://www.sqlite.org/datatype3.html


Actually, 'text' is not a designated keyword; see
. In any
event, being a keyword would not explain why the
query with 'or' works and the one with 'and' does
not, or why both queries work in older versions of
Sqlite3.

This seems to be a recently introduced change in
behavior; whether it's a bug is perhaps a matter
of opinion.

Regards

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

[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 from table2,
so I would expect that you want to insert the missing
records into table2. Assuming that's what you meant,

  insert into table2
  select * from table1
  where table1.field not in (select field from table2)

or

  insert into table2
  select * from table1
  where not exists
(select field from table2 where table2.field=table1.field)

The subquery in the first form is static (executed only
once). The subquery in the second form is correlated
(executed many times), but the second form can be
faster in some circumstances.

Regards

Re: [sqlite] Easyer way ?

[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] "Data Dictionary" in SQLite?

[Kurt Welgehausen]

pragma table_info (and others).

Look at the pragma docs.


Regards

Re: [sqlite] help with GROUP BY

[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 group by ttl) t2
  where table.title = t2.ttl and table.speed = t2.maxsp

Regards

Re: [sqlite] index organized table +compression

[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 create an index on all your columns,
SQLite will sometimes execute a select using only the
index, but you'll be storing your data twice.

Regards

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

[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] sqlite on embedded board

[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 memory.db ...

Regards

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

[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 exec().

Regards

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

[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] All transactions are Database locks?

[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 everyone make databases with one table each ... ?

No. Fetch your data, finalize the select, then do your
update(s). If it's important that the database not be
changed by another process between the select and the
update, wrap the whole sequence in a transaction (begin
immediate).

 

Regards

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

[Kurt Welgehausen]

Read the first 31 bytes of the file (or the first 15 bytes
in V3).

Regards

Re: [sqlite] Null values in Primary Key

[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 my own check for an existing record instead of
> using insert or replace?

If you're going to allow nulls in primary keys, SQLite's behavior
is correct; nulls are not equal to each other for the purpose of
determining column uniqueness -- so the answer to your question
is yes if you're going to allow nulls.


Regards

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

[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 result from

   select * from prim as p
   where not exists (select refid from ref1
 where refid=p.id) and
 not exists (select refid from ref2
 where refid=p.id)

I would guess that this form would be more efficient
because if the first test fails, the second sub-select
should not be executed, perhaps saving a complete scan
of ref2.

Regards

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

[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] SQL for finding non-matching rows

[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.


Regards

Re: [sqlite] manipulating new.? in triggers

[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] How to update wiki? date/time page misprint

[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] Index and General Optimization Question

[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] possible documentation error

[Kurt Welgehausen]

AS is always optional, i.e., in table names and in
column names.

Regards

Re: [sqlite] Finding max of each group?

[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 messages table has a column called msg_id,

  select t1.conversation_id, t2.c, t2.mun, t2.mup, t1.msg_id
  from messages t1,
   (select conversation_id cid, count(*) c,
   max(unread) mun, max(updated_on) mup
from messages
where cid in ()
group by cid) t2
  where t1.conversation_id = t2.cid and t1.updated_on = t2.mup


Regards

Re: [sqlite] RFC Beginning digit in column name

[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] how to get notice when database is free after it was busy

[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 remove the !) -- also, you don't really need
the 'set selectResult', although it's sometimes
convenient, because if the script succeeds, the result
will be in the supplied variable (catchErr in this
case) -- also, it's 'db eval', not 'eval db'.

Regards

Re: [sqlite] quoting

[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 byte may solve
the problem. If that doesn't work, you may have to use
hexadecimal codes.

insert into table ... values (x'61626300646566')

should always insert "abcdef" into the table.


Regards

Re: [sqlite] ALTER TABLE: Confusing documentation

[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] SQLite 3.2 install on Mac OS X

[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 archives; you might find a
solution spelled out:

   http://www.mail-archive.com/sqlite-users@sqlite.org/


Regards

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

[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] Limit how much data to read

[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] Calculation between rows?

[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 table, you're guaranteed to have a "hole" in
your keys.

You don't really need a manufactured key. Since your arithmetic
depends on the timestamp being unique, you can use the timestamp
as your primary key; but you should define the key to enforce
existence and uniqueness:

create table bytes (ts int primary key, bytes int);

insert into bytes (ts, bytes) values (1121484797, 11234);
insert into bytes (ts, bytes) values (1121484923, 14583);
insert into bytes (ts, bytes) values (1121485008, 19267);
insert into bytes (ts, bytes) values (1121484801, 12789);
insert into bytes (ts, bytes) values (1121485051, 23005);
insert into bytes (ts, bytes) values (1121485832, 28436);

select datetime(ts, 'unixepoch'), bytes from bytes
order by ts;
||
\/
datetime(ts, 'unixepoch')  bytes
-  --
2005-07-16 03:33:1711234
2005-07-16 03:33:2112789
2005-07-16 03:35:2314583
2005-07-16 03:36:4819267
2005-07-16 03:37:3123005
2005-07-16 03:50:3228436

select datetime(ts, 'unixepoch'), bytes from bytes
where datetime(ts, 'unixepoch') between
'2005-07-16 03:35:01' and '2005-07-16 03:40:00'
order by ts;
||
\/
datetime(ts, 'unixepoch')  bytes
-  --
2005-07-16 03:35:2314583
2005-07-16 03:36:4819267
2005-07-16 03:37:3123005

select datetime(t2.ts, 'unixepoch') 'time',
   (0.0+t2.bytes-t1.bytes)/(t2.ts-t1.ts) 'byte rate'
from bytes t1, bytes t2
where t1.ts =
   (select max(t3.ts) from bytes t3 where t3.ts < t2.ts)
and datetime(t2.ts, 'unixepoch') between
  '2005-07-16 03:35:01' and '2005-07-16 03:40:00'
order by t2.ts ;
||
\/
time byte rate
---  
2005-07-16 03:35:23  14.7049180327869
2005-07-16 03:36:48  55.1058823529412
2005-07-16 03:37:31  86.9302325581395

But SQL is not designed to do sequential processing of data.
You'll probably get better performance and more readable code if
you retrieve your data set with SQL

select bytes, ts from bytes
where datetime(ts, 'unixepoch') between
'2005-07-16 03:35:01' and '2005-07-16 03:40:00'
order by ts;

and use your host language to do the arithmetic.


Regards

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

[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 returned,
 not the number of bytes.

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

[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 this?
> >
> > I'm just a NOOB, looking to see what all SQLite can do for me, before
> > I go too far down this road.
> >
> > Thx.
>
> like this?
> select * from table where a like '%abc%';
>
SQLite does not support full-text searches. This has been
discussed on the list before. I don't know of any plans
to add it, but maybe someone more familiar with develop-
ment plans can give you a better answer; or you could
search the archives:

  


Regards

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

[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] Parsing bug with sqlite3.exe ?

[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 
--  --
1   One   
2   Two   
sqlite> select * from t1; -- comment ;
a   c 
--  --
1   One   
2   Two   
sqlite> -- comment
sqlite>


Regards

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

[Kurt Welgehausen]

Re: [sqlite] philosophy behind public domain?

[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.
> > 
> > I believe I have heard something like that as well.
> > 
>
> Sounds like a folk legend to me.

The idea is that there's no quid pro quo, and therefore
no contract, when you place something in the public
domain. If you have a license, you can require the user
to accept the software as-is in return your letting him
use it, and by using the software he implicitly accepts
the terms of the contract. Some lawyers apparently think
that using PD software does not imply acceptance of any
attached disclaimer. This was discussed briefly in an
article on licenses in Linux Journal some months ago.

On the other hand, I've never heard of any problem in
this regard. The Icon programming language (which is
under appreciated and under used) has been in the public
domain for 20 or 30 years, and I don't think they've
ever had such a problem, in spite of a buggy release or
two.

Regards

Re: [sqlite] unit tests

[Kurt Welgehausen]

> How do I run the unit tests in Linux?

make test

Re: [sqlite] nested functions in select

[Kurt Welgehausen]

> ... select count(distinct(something)) ...

   http://www.sqlite.org/omitted.html

BTW, distinct is not a function.

Regards

Re: [sqlite] Is threading enabled?

[Kurt Welgehausen]

> How can i find out ... whether threading is enabled ... ?

grep -in thread Makefile


Regards

Re: [sqlite] Windows question

[Kurt Welgehausen]

> Am I right ... sqlite-3_2_1.zip ?

Yes, and get the same version of the dll, with or without
tcl bindings, if you're going to write programs.

Regards

Re: [sqlite] OT: Suitable table schema for object relationship

[Kurt Welgehausen]

> A --> B --> C
>   \
>\--> D --> E

You've drawn a tree, but your comments indicate that you
want something more general.

If you can restrict yourself to trees, you can probably
use nested sets (invented by Joe Celko). These are fairly
simple to manipulate. A Google search will turn up
articles explaining the method.

If you must have a more general graph, you'll probably
have to store all the paths you're interested in, e.g.,

   A/B
   A/B/C
   A/B/D
   A/B/D/E

If you're interested in paths that don't start at the
root, you'll need also

   B/C
   B/D
   B/D/E
   D/E

If you add an arc directly from A to E, you'll need A/E.

You could then get all the paths from A to E by searching
for Path LIKE 'A%E'. In this case, you'll probably want
to get all the paths you're interested in and then
analyze and manipulate them in your host language.

Things would be much simpler if you could limit yourself
to trees modeled as nested sets.

Regards

Re: [sqlite] strftime and the %f option

[Kurt Welgehausen]

> ... %f option to strftime ... retu[r]ns '000' ...

Is that always true, or just when the date is 'now'?
I suspect that 'now' is producing an integer.

sqlite> select strftime('%f', 'now');
strftime('%f', 'now')
-
52.000   
sqlite> select strftime('%f', 'now');
strftime('%f', 'now')
-
59.000   
sqlite> select strftime('%f', '10:11:22.33');
strftime('%f', '10:11:22.33')
-
22.329   


Regards

Re: [sqlite] NFS Query Performance

[Kurt Welgehausen]

SQLite is not a client-server database. In the worst case,
when you query a database file over NFS, all the tables in
your query are sent over the network to be processed on
your cpu; so if you want to select one row from a table of
a million rows, the million rows are retrieved over the
network (in the worst case).

You may want to consider a client-server database for this
reason and also because there have been reports that
locking does not work reliably on some implementations of
NFS -- so you could be risking a corrupted db file if you
have multiple users.

I think this has been discussed on the list. You could
try a search of the archive:




Regards

Re: [sqlite] Correlated Subqueries not working for me

[Kurt Welgehausen]

You don't need a correlated subquery:

sqlite> select * from tbl;
c1  c2
--  --
1   a 
1   a 
1   b 
1   a 
2   b 
2   c 
2   a 
3   c 
3   c 
sqlite> select c1, max(cnt12) from
   ...>   (select c1, count(*) cnt12 from tbl
   ...>group by c1, c2)
   ...> group by c1;
c1  max(cnt12)
--  --
1   3 
2   1 
3   2 


Regards

Re: [sqlite] multi threading

[Kurt Welgehausen]

> SELECT  id, code FROM a WHERE
> (code IN
> (SELECT code FROM
> (SELECT code, COUNT(code) AS c FROM  a GROUP BY code) AS aaa 
> WHERE  c > 1)
> )
>and ORDER BY code

The "and" in your code is illegal, but it is better to use

  select id, code from a
  where code in
(select code from a group by code having count(code) > 1)
  order by code

Regards

Re: [sqlite] malformed 2.1 db

[Kurt Welgehausen]

> Unable to open database 1.db
> now what? any suggestions?

There's probably nothing you can do to fix the file.

In the past, you could corrupt a database by vacuuming
it in one process and then doing an insert in another
process. This has been fixed in V3; I don't know if the
bug even existed in V2.

Most likely, you (or someone) did something to corrupt
the file. See Section 6 of .

Regards

Re: [sqlite] how to get the INTEGER PRIMARY KEY for the row just inserted?

[Kurt Welgehausen]

> begin immediate; insert; select max(id) from blah; commit;

Or "select last_insert_rowid() from blah limit 1"

Regards

Re: [sqlite] About field sizes...

[Kurt Welgehausen]

> What effect has dimension on field size? ...

See 
and .

The dimension(s) has no effect on the actual storage.

> And why is it valid when you mention "date" ... ?

The type in a create statement serves only to determine
the type affinity for the column. Specified type can be
anything. If 'date' is specified for the type, the
column will have type affinity 'numeric'. See the files
mentioned above.

Regards

Re: [sqlite] about ROWID after vacuum

[Kurt Welgehausen]

> Is it a bug? ...

No, it's not a bug.

Regards

Re: [sqlite] getting table column names and types programaticly

[Kurt Welgehausen]

> Anyone know how I can query the field names and types for a given table?

pragma table_info(tablename)

Re: [sqlite] NULL representation/empty value in query results?

[Kurt Welgehausen]

> Yeah! I've missed the default option. But is this SQL standard?

Yes.

> sqlite> insert into deftest (i, s) values (NULL,'');

This inserts NULL (not a string) into i and '' into s.
When you retrieve that row using tcl, both are represented
by empty strings.

Default values replace missing values in insert statements,
not null values.  To get your default 'NULL' into i, and an
empty string into s, use

 insert into deftest (s) values ('');

The coalesce function suggested by someone else is also
useful. It let's you have a string representation for NULL
that can be different in different queries, if that's
important to you.

Regards

Re: [sqlite] NULL representation/empty value in query results?

[Kurt Welgehausen]

> Is there a way to change the NULL representation ... ?

No. Tcl has no null value. Usually this is not a problem,
but if you really need to distinguish between a missing
value and an empty string, you can use default values.

sqlite> create table deftest (k integer primary key,
   ...>   i integer default '?',
   ...>   s char default '??');
sqlite> insert into deftest (i) values (11);
sqlite> insert into deftest (i, s) values (22, '');
sqlite> select * from deftest;
k   i   s 
--  --  --
1   11  ??
2   22


Regards

Re: [sqlite] How do I Register on sqlite.org

[Kurt Welgehausen]

You can record the ticket number when you originate the
ticket and check the status any time with
.

Or, just bookmark the url after you submit the ticket.

Or, you can scan (search) the timeline for the ticket
number to see what action has been taken.

If you lose the ticket number but remember about when
you submitted it, you can read the timeline around that
date and probably find your ticket from the descriptions.

Regards

Re: [sqlite] Should Unary + Cast to a Number?

[Kurt Welgehausen]

> Well, I might as well use the substr() function, then ...

Yes, unless you think %m is more expressive than 6,2 or
you want to extract more than one component.

sqlite> select * from datetest;
k   d  
--  ---
1   2005-03-17T16:21:30
2   2005-03-17T17:22:30
sqlite> select strftime('On %m/%d at %H:%M', dd) Arrival_time from
   ...> (select substr(d,1,10)||' '||substr(d,-8,8) dd from datetest) ;
Arrival_time 
-
On 03/17 at 16:21
On 03/17 at 17:22


Regards

Re: [sqlite] Should Unary + Cast to a Number?

[Kurt Welgehausen]

Sorry, I misunderstood the context.


sqlite> select * from datetest;
k   d  
--  ---
1   2005-03-17T16:21:30
sqlite> select strftime("%m", substr(d,1,10)||' '||substr(d,-8,8))  
   ...> from datetest;
strftime("%m", substr(d,1,10)||' '||substr(d,-8,8))
---
03
sqlite> select abs(strftime("%m", substr(d,1,10)||' '||substr(d,-8,8)))
   ...> from datetest;
abs(strftime("%m", substr(d,1,10)||' '||substr(d,-8,8)))

3

Re: [sqlite] Should Unary + Cast to a Number?

[Kurt Welgehausen]

> Yes, I know it supports it without the "T" ...

sqlite> select strftime("%Y-%m-%dT%H:%M:%S", 'now', 'localtime');
strftime("%Y-%m-%dT%H:%M:%S", 'now', 'localtime')
-
2005-03-17T16:21:30

Re: [sqlite] Is this possible in SQLite?

[Kurt Welgehausen]

> Is there a way to do the following:
>
> INSERT INTO acopy SELECT * FROM a WHERE id = 1 ( SET id = some value X );
> INSERT INTO bcopy SELECT * FROM b WHERE id = 1 ( SET id = X );

http://www.sqlite.org/lang_insert.html

sql-statement ::= INSERT [OR conflict-algorithm] INTO
   [database-name .] table-name [(column-list)]
   VALUES(value-list) |
  INSERT [OR conflict-algorithm] INTO
   [database-name .] table-name [(column-list)]
   select-statement

Regards