Simon Slavin slav...@bigfraud.org wrote:
On 7 Oct 2013, at 3:45pm, dean gwilliam mgbg25...@blueyonder.co.uk 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
Sam Carleton scarle...@miltonstreet.com 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(
[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
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 ...
http://sqlite.org/lang_createtable.html
what is going on here? I'm having three records with the
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
Igor Tandetnik [EMAIL PROTECTED] wrote:
RohitPatel rohitpatel-/[EMAIL PROTECTED]
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
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'
blins [EMAIL PROTECTED] wrote:
How to get column description from table using SQL
pragma table_info()
http://sqlite.org/pragma.html
Regards
... 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.
[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
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
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()
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:
Richard Battagline [EMAIL PROTECTED] wrote:
How do you unsubscribe?
Read the headers of any message from the group.
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
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
JP [EMAIL PROTECTED] wrote:
SQLite provides a way to get the N-th row given a SQL statement, with
LIMIT 1 and OFFSET N.
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
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
http://sqlite.org/capi3ref.html#sqlite3_total_changes
Regards
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
Jay Sprenkle [EMAIL PROTECTED] wrote:
Works ok here:
C:\Temp\sqliteImportsqlite3
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
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
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
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
examples of syntax ... than the syntax page from the home page
Try Googling for 'sql tutorial'.
Regards
select user_id from person p1, person p2 where p1.user_idp2.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.
... 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
There's no string type in SQL. Go to
http://www.sqlite.org/datatype3.html
and read section 2.1.
Regards
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
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
... 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
http://www.sqlite.org/datatype3.html
Anyone have any thoughts ... ?
You've already figured it out. For more info, see
http://www.sqlite.org/optoverview.html.
Regards
... 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
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
What are these constructs suppose to do?
x op all (select y from t where ...)
is equivalent to
not exists (select y from t where not (x op y) and ...)
Any and some are synonyms.
x op any (select y from t where ...)
is equivalent to
exists (select y from t where x op y and ...)
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
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
If the trigger were recursive, it would invoke
itself forever, even if it's not doing anything
because the where clause fails.
Regards
Can anyone guide me to where fdatasync should be?
www.mail-archive.com/sqlite-users@sqlite.org/msg10336.html
(maybe)
Regards
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
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
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
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
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]
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
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
pragma table_info (and others).
Look at the pragma docs.
Regards
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
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:
http://www.mail-archive.com/sqlite-users@sqlite.org/
... possible to create index organized tables within SQLite
Not really. If you create an
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
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().
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
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
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
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 not null to the
spec for each PK column.
Does that mean I need to
Read the first 31 bytes of the file (or the first 15 bytes
in V3).
Regards
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
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 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.
Is it possible to change the values of certain rows that
are inserted into the database? ...
I think everything you need is explained at
http://www.sqlite.org/lang_createtrigger.html.
If you don't understand how to get the current date in
SQLite, look at the wiki page.
Regards
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
Currently, indices are not used to look up either term
in an OR expression.
See http://www.sqlite.org/php2004/page-052.html.
You could build 2 separate 5-column indices and use a
union.
Regards
select conversation_id, count(*), max(unread), max(updated_on)
from messages
where conversation_id in (list of ids)
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
AS is always optional, i.e., in table names and in
column names.
Regards
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
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 !)
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
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
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
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
Where in the documentation that explains how to use the sqlite substr()
function?
Go to the SQLite Syntax page, and click on 'expression'.
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,
SELECT avg(length(domain)) from domains;
The response is 10 and not 4.
From http://www.sqlite.org/lang_expr.html:
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
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?
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
The problem seems to be that sqlite is not recognizing
newline 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
www.mail-archive.com/sqlite-users@sqlite.org/msg08319.html
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
How do I run the unit tests in Linux?
make test
... select count(distinct(something)) ...
http://www.sqlite.org/omitted.html
BTW, distinct is not a function.
Regards
How can i find out ... whether threading is enabled ... ?
grep -in thread Makefile
Regards
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
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
... %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');
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
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
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
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
begin immediate; insert; select max(id) from blah; commit;
Or select last_insert_rowid() from blah limit 1
Regards
What effect has dimension on field size? ...
See http://www.sqlite.org/datatypes.html
and http://www.sqlite.org/datatype3.html.
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
Is it a bug? ...
No, it's not a bug.
Regards
Anyone know how I can query the field names and types for a given table?
pragma table_info(tablename)
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
You can record the ticket number when you originate the
ticket and check the status any time with
http://www.sqlite.org/cvstrac/tktview?tn=tkt#.
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.
strftime doesn't support the ISO-8601 format ...
I does if you give it the correct format string.
Regards
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
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
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)||'
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
1 - 100 of 197 matches
Mail list logo