The SQLite ALTER TABLE command is somewhat limited. For one thing, it cannot be used to rename an existing column on a table. But there is a workaround.
The trick is to create a new table with the columns named the way you want and then copy the data from the old table to the new table.
For example, suppose you have a table like this:
CREATE TABLE team(Name TEXT, Coach TEXT, City TEXT)
You later realize that the City column ought to instead by called Location.
Step 1: Rename the original table:
ALTER TABLE team RENAME TO team_orig;
Step 2: Create the replacement table with the original name and corrected column name:
CREATE TABLE team(Name TEXT, Coach TEXT, Location TEXT);
Step 3: Copy the data from the original table to the new table:
INSERT INTO team(Name, Coach, Location) SELECT Name, Coach, City FROM team_orig;
Note: The above command should be all one line.
Step 4: Drop the original table:
DROP TABLE team_orig;
With these four steps you can manually change any SQLite table. Keep in mind that you will also need to recreate any indexes, viewers or triggers on the new table as well.
Be sure to follow us on Twitter (@xojo) for more Xojo tips and techniques.