How to delete a field or row in Spatialite GUI

Question

I am trying to find an easy way to remove columns/fields from an existing QGIS Spatialite database file. I am new to both Spatialite GUI and SQL, but I want to get the said job done. I right-clicked on a layer (for-China) and chose 'Show columns' from the context menu. Then I got an error message:

SQL error: "near "-": syntax error"

so I tried executing the statement:

PRAGMA table_info('for-China');
alter table 'for-China'
delete row 'note';

and the table showed up, but the NOTE row wasn't deleted:

I tried using COLUMN instead of ROW and also tried using DROP instead of DELETE but NOTE is still left untouched. I am confused on what to do to delete the NOTE row.

Answer 1

I assume that spatialite uses the same escape characters as SQLite. Hence, try double quotes:

PRAGMA table_info("for-China");

alter table "for-China" drop column note;

You should only need this for identifiers that are keywords or use characters other than alpha numeric, underscore (and perhaps a few others).

SQLite also recognizes backticks and square braces, as explained in the documentation.