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?
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');
...
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 ...
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
...
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 ...
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 &...
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')
);
...
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 ...
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;
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
...
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 ...
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-...
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?
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:
...
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 ...
155
votes
3
answers
62k
views
How to rename a table in SQLite 3.0?
How do you rename a table in SQLite 3.0?
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 <= '...
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 "".
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 ...
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)
...
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 ...
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.
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:
...
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 ...
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 ...
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 *
...
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 ...
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 ...
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 ...
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.
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 ...
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 ...
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 ...
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 ...
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?
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 ...
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'...
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&...
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 ...
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 ...
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 ...
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?
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 ...
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.
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 ...
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?
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?
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,...
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 ...