How to drop a column in SQLite 3

As you might know, SQLite 3 does not support dropping columns from tables with ALTER TABLE command so we have to do a workaround instead. The idea is to create a new table with all the columns (and data) that we want to keep and then drop the old table and rename the new table with the old name.
Here is an example:

First we create a new table with all the columns and data we want to save

CREATE TABLE my_table_temp AS (SELECT id, user_id, latitude, longitude FROM my_table);

Then we drop the old table

DROP my_table;

And finally we rename our new table with the old name

ALTER TABLE my_table_temp RENAME TO my_table

Done!

Tested on OSX 10.7.5 and SQLite v3.7.11

  1. WARNING!!!

    Make sure you also take care of the Indexes and Views (if you have any)!!!

    Perform a .schema ‘Name Of Your Table’ BEFORE you drop the table!!!

    Use this info to re-create the Indexes and Views after renaming the table back to its original name!

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre lang="" line="" escaped="" cssfile="">

This site uses Akismet to reduce spam. Learn how your comment data is processed.