Simple DELETE statement didn't work
I have an MRU table that has 3 columns.
(VALUE varchar(255); TYPE varchar(20); DT_ADD datetime)
It is a table simply storing a record and a record of the date it was written. I would like to do this: delete the oldest entry when I add a new entry that is greater than a certain number.
Here is my request:
delete from MRU
where type = 'FILENAME'
ORDER BY DT_ADD limit 1;
Error message: SQL error: next to "ORDER": syntax error ...
The request returns an error.
a source to share
I'm not saying you should do this as it is completely non-portable, but if there is an compelling need, this will work:
In SQLite, the rowid column always exists unless an integer primary key is defined elsewhere. It can be used something like this:
delete from MRU where rowid = (
select rowid from MRU order by DT_ADD limit 1
)
a source to share
First of all, it always helps you post as much information as you have. In this particular case, the "error" is useless and it would take you 2 seconds to copy and paste the actual error message, which will give us valuable hints in helping.
Instead, I went to the documentation for the SQLite DELETE statement, found it here and noticed that lo and behold, DELETE does not have an ORDER BY unless it is compiled in a certain way. I am assuming your version is not there, although it's hard to tell without an error message.
You can try this instead:
delete from MRU where DT_ADD = (
SELECT MIN(DT_ADD) FROM MRU WHERE type = 'FILENAME'
)
a source to share