How to Drop a Column in an SQLite Table?

Modify SQLite table: delete column

When working with a SQLite database, you may need to delete columns from a table. Although it is intuitive to use the "ALTER TABLE table name DROP COLUMN column name" query, users may encounter errors.

Causes and Solutions

Until recently, SQLite did not directly support deleting columns. However, the latest versions (3.35.0 and above) now provide this functionality.

Prior to this update, modifying the table structure required a more complex approach:

1. Create a temporary table to store existing data.
2. Delete the original table.
3. Recreate the table with the desired column structure.
4. Copy the data back to the new table.

The following steps outline the process of using SQL syntax:

<code class="language-sql">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;</code>

Updated solution using DROP COLUMN support

With the latest SQLite version, this process has been greatly simplified. Columns can now be deleted directly using the following query:

<code class="language-sql">ALTER TABLE 表名 DROP COLUMN 列名</code>

This command will permanently delete the specified column from the table. Note that the result will lose any data previously stored in the column.

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