Note that as of version 3.25.0 released September 2018 you can now use ALTER TABLE to rename a column. Original “create new and drop old table” answer below. Say you have a table and need to rename “colb” to “col_b”: First create the new table with a temporary name, based on the old table definition … Read more
From http://www.sqlite.org/lang_createtable.html: CREATE TABLE IF NOT EXISTS some_table (id INTEGER PRIMARY KEY AUTOINCREMENT, …);
Have a look at http://sqlite.org/lang_conflict.html. You want something like: insert or replace into Book (ID, Name, TypeID, Level, Seen) values ((select ID from Book where Name = “SearchName”), “SearchName”, …); Note that any field not in the insert list will be set to NULL if the row already exists in the table. This is why … Read more
NOTE: Before trying this code, please find this line in the below code: private static String DB_NAME =”YourDbName”; // Database name DB_NAME here is the name of your database. It is assumed that you have a copy of the database in the assets folder, so for example, if your database name is ordersDB, then the … Read more
I found your question while searching for information with SQLite and Java. Just thought I’d add my answer which I also posted on my blog. I have been coding in Java for a while now. I have also known about SQLite but never used it… Well I have used it through other applications but never … Read more
You can use COLLATE NOCASE in your SELECT query: SELECT * FROM … WHERE name=”someone” COLLATE NOCASE Additionaly, in SQLite, you can indicate that a column should be case insensitive when you create the table by specifying collate nocase in the column definition (the other options are binary (the default) and rtrim; see here). You … Read more
There is no native boolean data type for SQLite. Per the Datatypes doc: SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).
SELECT * FROM Table_Name LIMIT 5;
So I did some tests with sqlite for very large files, and came to some conclusions (at least for my specific application). The tests involve a single sqlite file with either a single table, or multiple tables. Each table had about 8 columns, almost all integers, and 4 indices. The idea was to insert enough … Read more
You have two options. First, you could simply add a new column with the following: ALTER TABLE {tableName} ADD COLUMN COLNew {type}; Second, and more complicatedly, but would actually put the column where you want it, would be to create the new table with the missing column and a temporary new name: CREATE TABLE {tempNewTableName} … Read more