DROP Syntax error in SQLiteManager

Question

I have downloaded sqlite manager app in Firefox browser and open sqlite manager app.

1) Created database named DBSQLTEST.

2) Created table named SQLTEST contain has threes fields are SLNO, NAME and AGE

3) Inserted new records

But i want remove a ‘AGE’ column in sqltest table

i using sql command like below

ALTER TABLE SQLTEST DROP COLUMN AGE

SQLiteManager message says

SQLiteManager: Likely SQL syntax error: ALTER TABLE SQLTEST DROP COLUMN AGE [ near "DROP": syntax error ] Exception Name: NS_ERROR_FAILURE Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement]

What is error that?

score 10 · Accepted Answer · answered Jan 09 '14 at 11:44

10

sqlite does not support DROP COLUMN in ALTER TABLE. You can only rename tables and add columns.

Reference: http://www.sqlite.org/lang_altertable.html

If you need to remove columns, create a new table, copy the data there, drop the old table and rename the table to its intented name.

answered Jan 09 '14 at 11:44

laalto

150,114
66
286
303

how we can to remove a column in table? – Taj rajan Jan 09 '14 at 11:46
1

It seems sqlite now does support DROP COLUMN, but it's somewhat limited. It's explained on the page you linked (probably updated). – MightyPork Mar 18 '21 at 17:11
3

For completion: https://sqlite.org/lang_altertable.html#altertabdropcol SQLite 3.35.0 introduced support for `ALTER TABLE DROP COLUMN`. – Julian Dec 07 '21 at 11:08

score 7 · Answer 2 · answered Jan 09 '14 at 11:48

SQLite does not fully support ALTER TABLE statements. You can only rename table, or add columns.

If you want to drop a column, your best option is to create a new table without the column, and to drop the old table in order to rename the new one.

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;

Credits to Benoit.

score 2 · Answer 3 · answered Jan 09 '14 at 11:53

Create a table

CREATE TABLE SQLTEST ("SLNO" INTEGER PRIMARY KEY ,"NAME" TEXT,AGE INTEGER)

If want to remove a column

ALTER TABLE SQLTEST RENAME TO SQLTESTCOPY

CREATE TABLE SQLTEST ("SLNO" INTEGER PRIMARY KEY ,"NAME" TEXT)

INSERT INTO SQLTEST"SELECT "SLNO","NAME" FROM SQLTESTCOPY

DROP TABLE SQLTESTCOPY

easy.

DROP Syntax error in SQLiteManager

3 Answers3