Delete column from SQLite table

sql sqlite ddl
sandy picture sandy · May 9, 2011 · Viewed 121.2k times · Source

I have a problem: I need to delete a column from my SQLite database. I wrote this query

alter table table_name drop column column_name

but it does not work. Please help me.

Answer

MeBigFatGuy picture MeBigFatGuy · May 13, 2011

From: http://www.sqlite.org/faq.html:

(11) How do I add or delete columns from an existing table in SQLite.

SQLite has limited ALTER TABLE support that you can use to add a column to the end of a table or to change the name of a table. If you want to make more complex changes in the structure of a table, you will have to recreate the table. You can save existing data to a temporary table, drop the old table, create the new table, then copy the data back in from the temporary table.

For example, suppose you have a table named "t1" with columns names "a", "b", and "c" and that you want to delete column "c" from this table. The following steps illustrate how this could be done:

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;

Related questions

How do I add a foreign key to an existing SQLite table?

I have the following table: CREATE TABLE child( id INTEGER PRIMARY KEY, parent_id INTEGER, description TEXT); How do I add a foreign key constraint on parent_id? Assume foreign keys are enabled. Most examples assume you're creating the table …

Read More
Add unique index. SQLite3

I need to add a unique field index to an existing table. I made this row: ALTER TABLE auth_user ADD UNIQUE INDEX (email); The table and the field are already exist. The error is: Query Error: near "UNIQUE": syntax …

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More