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 columns if the record exists, If it does not exists I want to INSERT the record with the default (NUL) value for … Read more
In MySQL you can insert multiple rows like this: INSERT INTO ‘tablename’ (‘column1’, ‘column2’) VALUES (‘data1’, ‘data2’), (‘data1’, ‘data2’), (‘data1’, ‘data2’), (‘data1’, ‘data2’); However, I am getting an error when I try to do something like this. Is it possible to insert multiple rows at a time in an SQLite database? What is the syntax … Read more
What is the syntax for specifying a primary key on more than 1 column in SQLITE ? 9 s 9 According to the documentation, it’s CREATE TABLE something ( column1, column2, column3, PRIMARY KEY (column1, column2) );
What would be considered the best practices when executing queries on an SQLite database within an Android app? Is it safe to run inserts, deletes and select queries from an AsyncTask’s doInBackground? Or should I use the UI Thread? I suppose that database queries can be “heavy” and should not use the UI thread as … Read more
How do I, reliably, check in SQLite, whether a particular user table exists? I am not asking for unreliable ways like checking if a “select *” on the table returned an error or not (is this even a good idea?). The reason is like this: In my program, I need to create and then populate … Read more
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? 1 17
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 Output: id customer total 1 Joe 5 2 Sally 3 3 Joe 2 4 Sally 1 I’d like … Read more
Optimizing SQLite is tricky. Bulk-insert performance of a C application can vary from 85 inserts per second to over 96,000 inserts per second! Background: We are using SQLite as part of a desktop application. We have large amounts of configuration data stored in XML files that are parsed and loaded into an SQLite database for … Read more
Thanks to user phew for the help/ideas. I missed the obvious command line instructions on Xerial’s site for the Sample program. To get the program to run from the command line, I had to copy the JAR file into the same folder as the .CLASS file. Then run the following command: java -classpath “.:sqlite-jdbc-(VERSION).jar” Sample … Read more
