Removing columns from SQLite Databases

For a while I’d been annoyed that my SQLite database for Flagster contained a few dead-end columns. Since SQLite does not allow you to drop columns, I was resigned to having them gathering dust, and space.

However searching for unrelated help, came across a neat trick for removing columns. Although it takes three stages.

This was all tested/completed using Base, arguably the best SQLite editor on the Mac. Make sure you take a backup before starting though, just in case.

(1) duplicate table (without the unwanted columns) as a new table in your database

To create a new table from an old one, use a combination of CREATE and SELECT commands:

CREATE TABLE newTable AS
  SELECT col1, col2, col3, col4 FROM oldTable;

Just only list the columns you want in the SELECT statement, and list them in the order you want them in the table. For bonus points you can also sort the records as they’re written to the table.

CREATE TABLE newTable AS
  SELECT col1, col2, col3, col4 FROM oldTable
  SORT BY col2;

(2) drop the old table (with the unwanted columns)

Once confident your newTable has all the data you need, you can remove the old one. This is simple using the DROP command:

DROP TABLE oldTable;

(3) rename the new table with the old name

Finally, to tidy up and save updating any existing code, rename the new table to match the old table name:

ALTER TABLE newTable
  RENAME TO oldTable;

And that’s it. Three steps later you’ve cleaned up your database, sorted your records, and made everything right in the world.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>