SQLite: Simple DELETE statement did not work

Question

I have a table MRU, that has 3 columns.

(VALUE varchar(255); TYPE varchar(20); DT_ADD datetime)

This is a table simply storing an entry and recording the date time it was recorded. What I wanted to do is: delete the oldest entry whenever I add a new entry that exceeds a certain number.

here is my query:

delete from MRU 
where type = 'FILENAME' 
ORDER BY DT_ADD limit 1;

The error message is: SQL Error: near "ORDER": syntax error ...

The query returns an error.

Thanks

Answer 1

First of all, it always helps posting as much information as you have. In this particular instance, "an error" is unhelpful and it would've taken you perhaps 2 seconds to copy and paste the actual error message given, which would give us valuable clues when helping you.

Instead, I went to the documentation for the DELETE statement of SQLite, found here, and notice that lo and behold, DELETE does not have ORDER BY, unless it is compiled in a specific manner. I assume your version isn't, although without the error message it is hard to tell.

What you can try instead is this:

delete from MRU where DT_ADD = (
    SELECT MIN(DT_ADD) FROM MRU WHERE type = 'FILENAME'
)

Answer 2

I'm not saying that you should do so, since it's completely non-portable, but if there's a compelling need, this will work:

In SQLite the rowid column always exists unless an integer primary key is defined elsewhere. This can be used in something like:

delete from MRU where rowid = (
    select rowid from MRU order by DT_ADD limit 1
)