[SOLVED] alter table drop column syntax error using sqlite

alter table drop column syntax error using sqlite

This is the schema of my table:

create table LPCG(ID integer primary key, PCG text, Desc text, test text);

I wish to drop the column "test", and hence use the command:

alter table LPCG drop column test;

This is the error message I get:

Error: near "drop": syntax error

Can someone please help me correct my error?

An additional question is: I understand that ID is the primary key attribute. Would I be able to drop that column? If not, is there a workaround which anyone has used?

Thanks in advance for any help.

Solution

Up to version 3.35, SQLite did not support ALTER TABLE DROP COLUMN statements. You could only rename table, or add columns.

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

As of 2021, ALTER TABLE support is still limited but includes dropping a column, under conditions.