Simple DELETE statement did not work

I have a table MRU, that has 3 columns.

(VALUE varchar(255); TYPE varchar(20); DT_ADD da开发者_StackOverflow社区tetime)

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.

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'
)

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
)