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:

<code class="language-sql">ALTER TABLE table_name DROP COLUMN column_name;</code>

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:

<code class="language-sql">CREATE TEMPORARY TABLE t1_backup (a, b);
INSERT INTO t1_backup SELECT a, b FROM t1;</code>

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

<code class="language-sql">DROP TABLE t1;</code>

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

<code class="language-sql">CREATE TABLE t1 (a, b);</code>

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

<code class="language-sql">INSERT INTO t1 SELECT a, b FROM t1_backup;</code>

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

<code class="language-sql">DROP TABLE t1_backup;</code>

6. Commit changes: Make changes permanent:

<code class="language-sql">COMMIT;</code>

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!