Deleting rows from SQLite table when no match exists in another table

Since you going down the route of subquery, might as well get rid of the join altogether and simplify the query:

DELETE FROM cache WHERE id NOT IN (SELECT id from main);

SQLite apparently doesn't support joins with the delete statement, as you can see on the Syntax diagrams. You should however be able to use a subquery to delete them.

ie.

DELETE FROM cache WHERE id IN
(SELECT cache.id FROM cache LEFT JOIN main ON cache.id=main.id WHERE main.id IS NULL);

(Not tested)