SQLite: delete a record for its ID: no such column error

Why:

DELETE FROM Notifiche WHERE ID = 11;

gives me this error (also directly in DB Browser for SQLite) ?

no such column: Notifiche.ID

Notifiche is my Table and ID the ID of that table …

Hi,

the WHERE clause is to determine the row(s) to delete. You need to supply a column name and the value.

The error states that table Notifiche has no column called ID.

Here https://www.techonthenet.com/sqlite/delete.php#:~:text=%20SQLite%3A%20DELETE%20Statement%20%201%20Description.%20The,are%20deleting%20the%20entire%20row%20from...%20More%20 you will find documentation of DELETE for SQlite.

its maybe written as Id or id?

The column name (it’s the autoincremental integer value, first column: is it valid as column, isn’t it?)
Its name is ID.
Here seems to be possible to delete a record for its ID, or i’m wrong ?

my meaning was case-sensitive or case-insensitive …

Yes @MarkusR, thanks, i’ve understood.
It’s all UPPERCASE: ID.
Why it doesn’t work ?

ah, you said its also in db browser, that is odd …

hmm, don’t know. you open the correct database? have you add this id field maybe later?
have you checked the folderitem native path for this db file you use?
i tested it with 2019r3.1 and its without problems for a windows 10 desktop project.

i just use this for a text

DB = database1

DB.ExecuteSQL “DELETE FROM Table1 WHERE Id = 3;”
DB.ExecuteSQL “DELETE FROM Table1 WHERE ID = 3;”

Var rows As RowSet = DB.SelectSQL(“SELECT * FROM Table1”)
For Each r As DatabaseRow In rows
System.DebugLog r.Column(“ID”)
Next

have you written id with spaces in db browser??? its possible

Yes in fact, if it not works in DB Browser then it should be ‘only’ a problem of query or other stuff only DB related, isn’t it ?

When created the table i don’t use spaces in ‘ID’ column.
In my query i’ve tried with

DELETE FROM Notifiche WHERE ID = 11;
DELETE FROM Notifiche WHERE ID= 11;
DELETE FROM Notifiche WHERE ID =11;

And the result is always as said …

1 Like

Can you use the sqlite3 CLI program to open the database and enter:

.schema

as the the command? What does it give?

CREATE TABLE “Notifiche” ( “ID” INTEGER NOT NULL, “Cronologico” TEXT, “Destinatario” TEXT, “Via” TEXT, “Civico” TEXT, “Cap” TEXT, “Citta” TEXT, “Provincia” TEXT, “Raccomandata” TEXT, “UfficioPostale” TEXT, “Giorno” TEXT, “Mese” TEXT, “Anno” TEXT, “Parte” TEXT, “Procuratore” TEXT, “UfficioGiudiziario” TEXT, “Pratica” TEXT, “Atto” TEXT, “Notificato” TEXT, “Duplicato” TEXT DEFAULT ‘NO’, “DGiorno” TEXT, “DMese” TEXT, “DAnno” TEXT, “Ticket” TEXT, PRIMARY KEY(“ID”) )

1 Like

could you remove and add this Id row again just for a test?

if there would be spaces the query looks like
DELETE FROM Notifiche WHERE "ID " = 11;

yes your table definition looks ok.

Do you mean remove the whole row (beginning with that ID) using DB Browser ?

yes you could copy the db file and play with to see what was the reason.

1 Like

YOU’RE RIGHT !!!
I cannot delete neither manually the row!
To complete and widen up the scenario I’ve created in that database a virtual table to allow full search FTS5 capabilities, and i’ve played also adding some triggers to make the real Notifiche table in sync with virtual one.
The triggers was created within the DB Browser.
At this point i’ve also to say that when i’ve created the triggers and saved the database DB Browser created a new file with the same name of my database but with an extension .sqbpro: what is that file ?
I’ve to admit that i’ve deleted it !!!
It could be the cause of all that mess ? :crazy_face:

I’d be inclined to get rid of all the double-quotes, which are not needed.

Sorry Tim, where i’ve inserted the double quotes ?

There’s all the double-quotes in the table definition.

Ahh, sure i see, if i well remember it was created by DB Browser, but i could be wrong.
Probably at this point the best is to recreate from scratch the database and try again.
Very last: do you think that the file created by DB Browser with the same name of database with the different extension .sqbpro can be deleted without creating issue ?

DB Browser should not add those double quotes, maybe are indeed part of the column names.

The query is correct if the column name is ID, but maybe there are some extra characters in the names.

1 Like