Modify SQLite table: Delete column

question:

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

ALTER TABLE table_name DROP COLUMN column_name;

However, it was not successful. What is the solution?

Answer:

In versions before SQLite 3.35.0 (2021-03-12), direct deletion of columns is not supported. To make such changes, a more complex approach is required:

1. Create temporary table: Copy all relevant data (for example, columns "a" and "b") into temporary table:

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

2. Delete the original table: Delete the table containing the unwanted columns:

DROP TABLE t1;

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

CREATE TABLE t1 (a, b);

4. Transfer data back: Insert data from a temporary table into the 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. Submit changes: Make changes permanent:

COMMIT;

renew:

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