How to delete rows from a table using 'LIMIT'

Question

I'm using FMDB in my iPhone app (pretty new to it!) and want to limit the number of rows in a table to a certain value. whenever I insert new records, I want to check if some maxNumberOfEntries is excessed and if so, delete the oldest entries. I tried the following:

(...)        
[db beginTransaction];
[db executeUpdate: @"DELETE FROM table_name WHERE someId = ? ORDER BY date LIMIT 10", [NSNumber numberWithInt: 123], nil];
[db commit];
(...)

but it seems like sqlite does not recognize the "LIMIT"-part of the query. How can I manage to delete a certain number of rows in a table without directly specifying the entry I want to delete?

LIMIT start,range so if you want to delete the first 30, do LIMIT 0,30 keep in mind that if you want to keep the first, and still want to delete 30 it becomes LIMIT 1,30 and not LIMIT 1,31 — Antwan van Houdt
– Antwan van Houdt, Commented Oct 23, 2011 at 13:35
does not work for me. without the limit-part i delete all entries with someId=123, with it i just delete nothing. — rindeastwood
– rindeastwood, Commented Oct 23, 2011 at 14:39

Community · Accepted Answer · 2017-05-23 11:51:21Z

1

I haven't tried this but it might be related.

There's a compile flag called SQLITE_ENABLE_UPDATE_DELETE_LIMIT which enables that feature:

http://sqlite.org/compile.html#enable%5Fupdate%5Fdelete%5Flimit

Here's a related SO post:

How do you enable LIMIT for DELETE in SQLite?

On a related post, you can use delete where id in a select statement and try limit in the select:

How can I delete data with specific row number (sqlite)

edited May 23, 2017 at 11:51

CommunityBot

11 silver badge

answered Oct 24, 2011 at 1:37

bryanmac

39.3k10 gold badges93 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to delete rows from a table using 'LIMIT'

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related