ALTER TABLE SQLite SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table. You can: … Read more
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
Try doubling up the single quotes (many databases expect it that way), so it would be : INSERT INTO table_name (field1, field2) VALUES (123, ‘Hello there”s’); Relevant quote from the documentation: A string constant is formed by enclosing the string in single quotes (‘). A single quote within the string can be encoded by putting … Read more
Encountered the same problem a while ago while writing a DB abstraction layer for C# and I never actually got around to finding out what the issue was. I just ended up throwing an exception when you attempted to delete a SQLite DB using my library. Anyway, this afternoon I was looking through it all … Read more
While you should probably use one of the many SQLite wrappers, if you wanted to know how to call the SQLite library yourself, you would: Configure your Swift project to handle SQLite C calls. If using Xcode 9 or later, you can simply do: import SQLite3 Create/open database. let fileURL = try! FileManager.default .url(for: .applicationSupportDirectory, … Read more
If most of those concurrent accesses are reads (e.g. SELECT), SQLite can handle them very well. But if you start writing concurrently, lock contention could become an issue. A lot would then depend on how fast your filesystem is, since the SQLite engine itself is extremely fast and has many clever optimizations to minimize contention. … 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
I missed that FAQ entry. Anyway, for future reference, the complete query is: SELECT name FROM sqlite_master WHERE type=”table” AND name=”{table_name}”; Where {table_name} is the name of the table to check. Documentation section for reference: Database File Format. 2.6. Storage Of The SQL Database Schema This will return a list of tables with the name … Read more
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
You can’t. SQLite doesn’t support JOINs in UPDATE statements. But, you can probably do this with a subquery instead: UPDATE closure SET checked = 0 WHERE item_id IN (SELECT id FROM item WHERE ancestor_id = 1); Or something like that; it’s not clear exactly what your schema is.