Drop column from SQLite table

by

Update: SQLite 2021-03-12 (3.35.0) now supports DROP COLUMN.

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;

More Related Contents:

  1. Delete column from SQLite table
  2. How do I add a foreign key to an existing SQLite table?
  3. Select first row in each GROUP BY group?
  4. How can I list the tables in a SQLite database file that was opened with ATTACH?
  5. SQLite Reset Primary Key Field
  6. Update table values from another table with the same user name
  7. How to pivot in SQLite or i.e. select in wide format a table stored in long format?
  8. How do I dump the data of some SQLite3 tables?
  9. What are DDL and DML?
  10. SQLite – How do you join tables from different databases?
  11. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  12. Does SQLite3 not support foreign key constraints?
  13. SQLite – ORDER BY RAND()
  14. Query to get records based on Radius in SQLite?
  15. How to use SQL Order By statement to sort results case insensitive?
  16. Sqlite: CURRENT_TIMESTAMP is in GMT, not the timezone of the machine
  17. sqlite alter table add MULTIPLE columns in a single statement
  18. Is there AUTO INCREMENT in SQLite?
  19. Drop all tables command
  20. Get month from DATETIME in sqlite
  21. How do I import .sql files into SQLite 3?
  22. DROP FUNCTION without knowing the number/type of parameters?
  23. sqlite equivalent of row_number() over ( partition by …?
  24. String concatenation does not work in SQLite
  25. SQLite3 UNIQUE constraint failed error
  26. Does SQLite3 have prepared statements in Node.js?
  27. PostgreSQL: Give all permissions to a user on a PostgreSQL database
  28. SQlite: select into?
  29. Copy table structure to new table in sqlite3
  30. ALTER TABLE without locking the table?

Leave a Comment