All Questions
14,026
questions
1377
votes
19
answers
1.2m
views
How can I list the tables in a SQLite database file that was opened with ATTACH?
What SQL can be used to list the tables, and the rows within those tables in an SQLite database file – once I have attached it with the ATTACH command on the sqlite3 command line tool?
- 50.9k
667
votes
26
answers
514k
views
How to insert multiple rows in SQLite?
In MySQL you can insert multiple rows like this:
INSERT INTO 'tablename' ('column1', 'column2') VALUES
('data1', 'data2'),
('data1', 'data2'),
('data1', 'data2'),
('data1', 'data2');
...
- 233k
625
votes
19
answers
387k
views
UPSERT *not* INSERT or REPLACE
http://en.wikipedia.org/wiki/Upsert
Insert Update stored proc on SQL Server
Is there some clever way to do this in SQLite that I have not thought of?
Basically I want to update three out of four ...
- 8,644
602
votes
25
answers
806k
views
How to get a list of column names on Sqlite3 database?
I want to migrate my iPhone app to a new database version. Since I don't have some version saved, I need to check if certain column names exist.
This Stackoverflow entry suggests doing the select
...
- 6,561
244
votes
9
answers
208k
views
Best way to work with dates in Android SQLite [closed]
I'm having some trouble working with dates on my Android application that uses SQLite.
I have a couple questions:
What type should I use to store dates in SQLite (text, integer, ...)?
Given the best ...
- 3,398
241
votes
8
answers
196k
views
Escape single quote character for use in an SQLite query
I wrote the database schema (only one table so far), and the INSERT statements for that table in one file. Then I created the database as follows:
$ sqlite3 newdatabase.db
SQLite version 3.4.0
Enter &...
- 16.7k
239
votes
8
answers
272k
views
How to create timestamp column with default value 'now'?
How to create a table with a timestamp column that defaults to DATETIME('now')?
Like this:
CREATE TABLE test (
id INTEGER PRIMARY KEY AUTOINCREMENT,
t TIMESTAMP DEFAULT DATETIME('now')
);
...
- 4,073
223
votes
14
answers
265k
views
How to dump the data of some SQLite3 tables?
How do I dump the data, and only the data, not the schema, of some SQLite3 tables of a database (not all the tables)?
The dump should be in SQL format, as it should be easily re-entered into the ...
199
votes
14
answers
197k
views
How do I add a foreign key to an existing SQLite table?
I have the following table:
CREATE TABLE child(
id INTEGER PRIMARY KEY,
parent_id INTEGER,
description TEXT);
How do I add a foreign key constraint on parent_id? Assume foreign keys are ...
- 76.4k
199
votes
3
answers
91k
views
sqlite alter table add MULTIPLE columns in a single statement
Is it possible to alter table add MULTIPLE columns in a single statement in sqlite?
The following would not work.
alter table test add column mycolumn1 text, add column mycolumn2 text;
- 22.9k
187
votes
16
answers
184k
views
How can I get dict from sqlite query?
db = sqlite.connect("test.sqlite")
res = db.execute("select * from table")
With iteration I get lists coresponding to the rows.
for row in res:
print row
I can get name of the columns
...
- 14.4k
165
votes
3
answers
132k
views
How to use SQL Order By statement to sort results case insensitive?
I have a SQLite database that I am trying to sort by Alphabetical order. The problem is, SQLite doesn't seem to consider A=a during sorting, thus I get results like this:
A
B
C
T
a
b
c
g
I want to ...
- 15k
164
votes
6
answers
146k
views
SQLite Reset Primary Key Field
I have a few tables in SQLite and I am trying to figure out how to reset the auto-incremented database field.
I read that DELETE FROM tablename should delete everything and reset the auto-...
- 5,139
162
votes
2
answers
179k
views
SQLite string contains other string query
How do I do this?
For example, if my column is "cats,dogs,birds" and I want to get any rows where column contains cats?
- 9,743
159
votes
20
answers
408k
views
SQLite in Android How to update a specific row
I've been trying to update a specific row for a while now, and it seems that there are two ways to do this. From what I've read and tried, you can just use the:
execSQL(String sql) method
or the:
...
- 18k
158
votes
11
answers
255k
views
Sqlite: CURRENT_TIMESTAMP is in GMT, not the timezone of the machine
I have a sqlite (v3) table with this column definition:
"timestamp" DATETIME DEFAULT CURRENT_TIMESTAMP
The server that this database lives on is in the CST time zone. When I insert into my table ...
- 8,082
155
votes
3
answers
62k
views
147
votes
14
answers
254k
views
SQLite DateTime comparison
I can't seem to get reliable results from the query against a sqlite database using a datetime string as a comparison as so:
select *
from table_1
where mydate >= '1/1/2009' and mydate <= '...
- 20.7k
146
votes
4
answers
171k
views
SQLite select where empty?
In SQLite, how can I select records where some_column is empty?
Empty counts as both NULL and "".
- 54.5k
144
votes
10
answers
230k
views
Declare variable in SQLite and use it
I want to declare a variable in SQLite and use it in insert operation.
Like in MS SQL:
declare @name as varchar(10)
set name = 'name'
select * from table where name = @name
For example, I will need ...
- 2,179
143
votes
5
answers
205k
views
How to import .sql files into SQLite3?
I have .sql files which have the following content:
#cat db.sql
create table server(name varchar(50),ipaddress varchar(15),id init)
create table client(name varchar(50),ipaddress varchar(15),id init)
...
- 46.1k
142
votes
5
answers
95k
views
String concatenation does not work in SQLite
I am trying to execute a SQlite replace function, but use another field in the function.
select locationname + '<p>' from location;
In this snip, the result is a list of 0s. I would have ...
- 68k
138
votes
11
answers
157k
views
Drop column from SQLite table
I have a problem: I need to delete a column from my SQLite database. I wrote this query
alter table table_name drop column column_name
but it does not work. Please help me.
- 1,918
127
votes
6
answers
310k
views
How do I insert datetime value into a SQLite database?
I am trying to insert a datetime value into a SQLite database. It seems to be sucsessful but when I try to retrieve the value there is an error:
<Unable to read data>
The SQL statements are:
...
- 20.7k
127
votes
3
answers
91k
views
SQLite - How do you join tables from different databases?
I have an application that uses a SQLite database and everything works the way it should. I'm now in the process of adding new functionalities that require a second SQLite database, but I'm having a ...
- 4,884
126
votes
3
answers
100k
views
SQLite - replace part of a string
Is it possible using SQL in an SQLite table to replace part of a string?
For example, I have a table where one of the fields holds the path to a file.
Is it possible to replace parts of the string so ...
- 3,063
123
votes
2
answers
143k
views
How to get first/top row of the table in Sqlite via Sql Query
I need to fetch the first/top row of a table in a Sqlite database.
But my program throws an SQLException "Sqlite Syntax Error: Syntax error near '1' " for the query that I am using:
SELECT TOP 1 *
...
- 2,009
116
votes
5
answers
121k
views
SQLite INSERT - ON DUPLICATE KEY UPDATE (UPSERT)
MySQL has something like this:
INSERT INTO visits (ip, hits)
VALUES ('127.0.0.1', 1)
ON DUPLICATE KEY UPDATE hits = hits + 1;
As far as I know this feature doesn't exist in SQLite, what I want to ...
- 153k
111
votes
4
answers
91k
views
converting int to real in sqlite
Division in sqlite return integer value
sqlite> select totalUsers/totalBids from
(select (select count(*) from Bids) as totalBids ,
(select count(*) from Users) as totalUsers) A;
1
Can we ...
- 9,471
109
votes
4
answers
70k
views
Deleting duplicate rows from sqlite database
I have a huge table - 36 million rows - in SQLite3. In this very large table, there are two columns:
hash - text
d - real
Some of the rows are duplicates. That is, both hash and d have the same ...
- 1,433
103
votes
11
answers
154k
views
Drop all tables command
What is the command to drop all tables in SQLite?
Similarly I'd like to drop all indexes.
- 14.6k
97
votes
7
answers
137k
views
How to copy data from one table to another (where both have other fields too that are not in common)?
I have 2 tables Source and Destination that have the same fields. ID and COUNTRY, though they both have other fields too that are not in common.
I need to copy the Source.Country value to the ...
- 68k
91
votes
6
answers
184k
views
Android SQLite: Update Statement
I need to implement SQLite in my application. I followed this tutorial:
Creating and using databases in Android one
Everything is working fine. I inserted 1 row with 5 columns.
Now I want to update ...
- 1,697
90
votes
10
answers
163k
views
How do I UPDATE a row in a table or INSERT it if it doesn't exist?
I have the following table of counters:
CREATE TABLE cache (
key text PRIMARY KEY,
generation int
);
I would like to increment one of the counters, or set it to zero if the corresponding row ...
- 4,266
89
votes
6
answers
102k
views
Update table values from another table with the same user name
I have two tables, with a same column named user_name, saying table_a, table_b.
I want to, copy from table_b, column_b_1, column_b2, to table_b1, column_a_1, column_a_2, respectively, where the ...
- 62.5k
88
votes
5
answers
62k
views
SQLite - ORDER BY RAND()
In MySQL I can use the RAND() function, is there any alternative in SQLite 3?
- 153k
86
votes
5
answers
59k
views
What are valid table names in SQLite?
What are the combination of characters for a table name in SQLite to be valid? Are all combinations of alphanumerics (A-Z, a-z and 0-9) constitute a valid name?
Ex. CREATE TABLE 123abc(...);
What ...
- 14.3k
85
votes
4
answers
163k
views
How to do IF NOT EXISTS in SQLite
I am trying to port this line from MS SQL Server to SQLite
IF NOT EXISTS(SELECT 1 FROM EVENTTYPE WHERE EventTypeName = 'ANI Received')
INSERT INTO EVENTTYPE (EventTypeName) VALUES ('ANI Received'...
- 60.7k
84
votes
9
answers
141k
views
Why does this SQLite query select all dates, even those outside of the criteria?
I am running sqlite to select data between two ranges for a sales report. To select the data from between two dates I use the following statement:
SELECT * FROM test WHERE date BETWEEN "11/1/2011&...
- 1,437
83
votes
3
answers
83k
views
Does sqlite support any kind of IF(condition) statement in a select
Does sqlite support the sql function "if" in the select statement?
for example
select if( length( a ) > 4 , a , ' ') as b
from foo
which would return a if the length was over 4 chars long. or ...
- 1,181
82
votes
5
answers
41k
views
SQlite: select into?
I'm not sure if I can use select into to import data from another table like this:
select * into
bookmark1
from bookmark;
Is it true that SQlite doesn't support this syntax? are there any ...
- 935
82
votes
10
answers
98k
views
SQLite Order By Date1530019888000
Every record in my SQLite database contains a field which contains a Date stored as a string in the format 'yyyy-MM-dd HH:mm:ss'.
Is it possible to query the database to get the record which ...
- 3,211
79
votes
5
answers
60k
views
How to do a FULL OUTER JOIN in SQLite?
SQLite only supports INNER JOIN and LEFT JOIN. How do I do a FULL OUTER JOIN?
- 30.8k
76
votes
9
answers
68k
views
What is an efficient way of inserting thousands of records into an SQLite table using Django?
I have to insert 8000+ records into a SQLite database using Django's ORM. This operation needs to be run as a cronjob about once per minute.
At the moment I'm using a for loop to iterate through all ...
user11617
74
votes
7
answers
94k
views
What is difference between SQLite and SQL
I know SQLite Data Base is used in mobile devices (Android, iPhone) and it is light, takes only Kb space. Is there any limitation in SQLite? I want to know how they are different.
- 822
73
votes
5
answers
108k
views
SQLite3 Integer Max Value
what is the maximum value of data type INTEGER in sqlite3 ?
How do you store ip address in database ?
What is attached ?
How to create table which belongs to a specific database using sql ddl?
What ...
- 2,700
73
votes
1
answer
67k
views
SQLite - SELECT TOP syntax error [duplicate]
I'm trying to use the statement SELECT TOP 1 * FROM tasks WHERE dueDate < ?1 ORDER BY dueDate DESC but SQLite says near "1": syntax error. What's wrong?
- 9,713
68
votes
6
answers
39k
views
Select random row(s) in SQLite
In MySQL, you can select X random rows with the following statement:
SELECT * FROM table ORDER BY RAND() LIMIT X
This does not, however, work in SQLite. Is there an equivalent?
- 2,398
67
votes
8
answers
196k
views
Python and SQLite: insert into table
Having the following table schema:
tablename(
name varchar[100],
age int,
sex char[1]
)
Having a list that has 3 rows each representing a table row:
row1 = [laks,444,M]
row2 = [kam,445,...
- 46.1k
66
votes
3
answers
43k
views
SQLite Query in non case sensitive alphabetical order [duplicate]
All I want to do is grab the stuff in alphabetical order and ignore the capital letters.
db.rawQuery("SELECT " + catName + " FROM "+tableName+" ORDER BY "+catName+" ASC COLLATE NOCASE;", null);
This ...
- 1,667