All Questions

Tagged with
Filter by
Sorted by
Tagged with
1730votes
20answers
1.5mviews

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My ...
user avatar
1298votes
17answers
1.1mviews

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 SQLite 3 command line tool?
user avatar
  • 47.5k
610votes
25answers
439kviews

Is it possible to insert multiple rows at a time in an SQLite database?

In MySQL you can insert multiple rows like this: INSERT INTO 'tablename' ('column1', 'column2') VALUES ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'); ...
user avatar
  • 210k
590votes
19answers
340kviews

SQLite - 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 ...
user avatar
  • 8,224
239votes
9answers
204kviews

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 ...
user avatar
  • 3,348
211votes
9answers
230kviews

sqlite database default time value 'now'

Is it possible in a sqlite database to craete a table that has a timestamp column that default to DATETIME('now') ? Like this: CREATE TABLE test ( id INTEGER PRIMARY KEY AUTOINCREMENT, t ...
user avatar
  • 3,773
197votes
13answers
235kviews

How do I 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 ...
181votes
4answers
77kviews

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;
user avatar
  • 19.9k
164votes
12answers
151kviews

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 ...
user avatar
154votes
16answers
139kviews

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 ...
user avatar
  • 11.9k
153votes
20answers
382kviews

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: ...
user avatar
  • 17.2k
152votes
3answers
115kviews

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 ...
user avatar
142votes
4answers
92kviews

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 ...
user avatar
  • 63.3k
141votes
5answers
120kviews

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-...
user avatar
  • 4,739
140votes
11answers
209kviews

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 ...
user avatar
  • 7,571

15 30 50 per page
1
2 3 4 5
876