How Can I Drop a Column from a SQLite Table?

Modify SQLite table: delete column

Question:

Try to delete a column from a SQLite database table using the following query:

ALTER TABLE table_name DROP COLUMN column_name;

However, there was no success. What's the solution?

Answer:

In versions prior to SQLite 3.35.0 (2021-03-12), directly deleting columns is not supported. To make such changes, a more sophisticated approach is required:

1. Create temporary table: Copy all relevant data (e.g. columns "a" and "b") into the temporary table:

CREATE TEMPORARY TABLE t1_backup (a, b);
INSERT INTO t1_backup SELECT a, b FROM t1;

2. Delete original table: Delete table with unnecessary columns:

DROP TABLE t1;

3. Recreate the original table: Define a new table that does not contain the deleted columns:

CREATE TABLE t1 (a, b);

4. Transfer data back: Insert data from temporary table into newly created table:

INSERT INTO t1 SELECT a, b FROM t1_backup;

5. Delete temporary table: Clean up by deleting temporary table:

DROP TABLE t1_backup;

6. Commit changes: Make changes permanent:

COMMIT;

Update:

SQLite 3.35.0 and later now directly supports the DROP COLUMN clause, making it easier to drop columns from tables.

The above is the detailed content of How Can I Drop a Column from a SQLite Table?. For more information, please follow other related articles on the PHP Chinese website!