All Questions

Tagged with
Filter by
Sorted by
Tagged with
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?
izb's user avatar
  • 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'); ...
Andrew's user avatar
  • 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 ...
Mike Trader's user avatar
  • 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 ...
luebken's user avatar
  • 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 ...
Filipe's user avatar
  • 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 &...
jpm's user avatar
  • 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') ); ...
Joep's user avatar
  • 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 ...
Dane O'Connor's user avatar
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;
user775187's user avatar
  • 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 ...
Meloun's user avatar
  • 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 ...
CodeFusionMobile's user avatar
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-...
Nathan's user avatar
  • 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?
Joren's user avatar
  • 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: ...
EGHDK's user avatar
  • 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 ...
BrianH's user avatar
  • 8,082
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?
readonly's user avatar
  • 349k
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 <= '...
Brad's user avatar
  • 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 "".
Timo Huovinen's user avatar
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 ...
Muhammad Nour's user avatar
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) ...
webminal.org's user avatar
  • 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 ...
Ian Vink's user avatar
  • 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.
sandy's user avatar
  • 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: ...
Brad's user avatar
  • 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 ...
Jumbala's user avatar
  • 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 ...
colin's user avatar
  • 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 * ...
Omayr's user avatar
  • 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 ...
Alix Axel's user avatar
  • 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 ...
vaichidrewar's user avatar
  • 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 ...
Patches's user avatar
  • 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.
alamodey's user avatar
  • 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 ...
Ian Vink's user avatar
  • 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 ...
vivek_Android's user avatar
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 ...
Remy Blank's user avatar
  • 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 ...
Bin Chen's user avatar
  • 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?
Alix Axel's user avatar
  • 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 ...
David's user avatar
  • 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'...
AngryHacker's user avatar
  • 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&...
Zombian's user avatar
  • 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 ...
Matt Peters's user avatar
  • 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 ...
Glaucon's user avatar
  • 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 ...
duncanportelli's user avatar
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?
Yada's user avatar
  • 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 ...
user avatar
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.
Deep Verma's user avatar
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 ...
nicholas's user avatar
  • 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?
ryyst's user avatar
  • 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?
Fahad Sadah's user avatar
  • 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,...
webminal.org's user avatar
  • 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 ...
Anthony Honciano's user avatar

1
2 3 4 5
281