Modern SQLite: Delete limit

This post is part of the Modern SQLite series, where I write about handy SQLite features you might not have heard about.

Bet you've never heard of it! The delete statement in SQLite supports order by and limit/offset clauses, so that you can limit the number of rows to be deleted.

This can be useful if there is (potentially) a lot to delete and you don't want the query to take forever (perhaps you will schedule such "limited" deletes to run regularly).

The order by combined with limit allows you to "prioritize" deletes (for example, delete older records first).

create table events(
    id integer primary key,
    ctime text,
    descr text
);

insert into events(ctime, descr)
values
('2024-05-20T12:34:00', 'event a'),
('2024-05-20T12:34:01', 'event b'),
('2024-05-20T12:34:02', 'event c'),
('2024-05-20T12:34:03', 'event d'),
('2024-05-20T12:34:04', 'event e');

-- delete 3 oldest events (a, b, c)
delete from events
order by ctime limit 3;

There is a big but though. For some reason, the SQLite authors don't include this feature in the amalgamation. So it's effectively unavailable to most third-party drivers and programs that use the amalgamation to compile SQLite.

Maybe one day the SQLite team will change their mind and we'll be able to limit our deletes! Until then, the feature requires building from source (not amalgamated) with a compile-time flag.

Documentation:

• Optional LIMIT and ORDER BY clauses
• SQLITE_ENABLE_UPDATE_DELETE_LIMIT

──

P.S. Like SQLite? Check out Redka — Redis re-implemented with SQLite.

★ Subscribe to keep up with new posts.