28

I need to delete rows from an SQLite table where their row IDs do not exist in another table. The SELECT statement returns the correct rows:

SELECT * FROM cache LEFT JOIN main ON cache.id=main.id WHERE main.id IS NULL;

However, the delete statement generates an error from SQLIte:

DELETE FROM cache LEFT JOIN main ON cache.id=main.id WHERE main.id IS NULL;

The error is: SQLite Error 1 - near "left": syntax error. Is there another syntax I could use?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Marek Jedliński
  • 7,088
  • 11
  • 47
  • 57
  • To explain: I am deleting rows from the "main" table using an additional where condition, to preserve any rows user has marked as "locked" (i.e., should not be deleted until unlocked): DELETE FROM main WHERE id = ? AND locked = 0; Each successful delete must be followed by deleting a matching row from the "cache" table, but SQlite does not return a value that would let me know whether the first delete statement actually matched any rows. So instead I tried to delete "unmatched" rows from the cache table, and snagged on the left join. – Marek Jedliński Feb 11 '11 at 08:59

3 Answers3

54

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)

laurent
  • 88,262
  • 77
  • 290
  • 428
wimvds
  • 12,790
  • 2
  • 41
  • 42
  • Thanks! I got very close to this before I read your answer, except I was using equals instead of IN, so my attempt didn't quite work as intended. – Marek Jedliński Feb 11 '11 at 16:56
20

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);
Igor Zevaka
  • 74,528
  • 26
  • 112
  • 128
  • 6
    Not an ideal solution if `main` contains millions of rows. Best to do the join first. – Jay Mar 28 '13 at 05:07
  • 2
    @Jacob Hopefully someone isn't doing millions of rows in sqlite in the first place. – fluffy Mar 08 '15 at 09:01
  • If your tables don't have primary keys, you can hack one on the fly, for example: `DELETE FROM cache WHERE x || ',' || y NOT IN (SELECT x || ',' || y from main);` – bonh May 12 '17 at 18:12
  • 4
    @fluffy, don't underestimate SQLite :) it's perfectly reasonable to use it to handle millions of rows, but even if it's a few thousands, this `SELECT id FROM` query is needlessly inefficient and slow. @winvds answer is a much better option. – laurent Nov 27 '17 at 22:29
  • `DELETE FROM cache WHERE NOT EXISTS (SELECT main.id from main WHERE main.id=cache.id)` – Brad Robinson May 25 '22 at 02:14
0

Solution from @wimvds didn't work for me, so I modified the query and removed WHERE condition:

DELETE FROM FirstTable WHERE firstTableId NOT IN (SELECT SecondTable.firstTableId FROM SecondTable LEFT JOIN FirstTable ON FirstTable.firstTableId=SecondTable.firstTableId)

This deletes all the rows from FirstTable that do not have their id assigned to any row in SecondTable.

Micer
  • 8,731
  • 3
  • 79
  • 73