Simple SQL question: where is the syntax error?

Question

sqlite> .schema autor

CREATE TABLE autor (ID INTEGER PRIMARY KEY, NOME TEXT, CIDADE TEXT, ESTADO TEXT, TELEFONE TEXT, idade number);

sqlite> alter table autor drop idade;

Error: near "drop": syntax error

(Also tried to use DROP COLUMN instead of DROP). Please help, is that a bug ?

Does this help? https://stackoverflow.com/questions/8442147/how-to-delete-or-add-column-in-sqlite — Isolated, Sep 28 '20 at 14:21
Yes! Thank you, I didn't know about that. Already tried to delete the question but I'm not allowed to. — Davi Tochetto Bove, Sep 28 '20 at 16:16

score 1 · Answer 1 · answered Sep 28 '20 at 14:20

You can't do this with SQLite, because it does not support dropping columns. This is a documented limitation (emphasis mine):

Only the RENAME TABLE, ADD COLUMN, and RENAME COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted.

A possible workaround is to recreate another table with the correct structure, then copy the data, drop the old table and rename the new table:

create table autor_new (
    id integer primary key, 
    nome text, 
    cidade text, 
    estado text, 
    telefone text
);
insert into autor_new select id, nome, cidade, estado, telefone from autor;
drop table autor;   -- back it up first!
alter table autor_new rename to autor;

Simple SQL question: where is the syntax error?

1 Answers1