Menu
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?
515
Deleting rows based on another table. Sometimes we need to delete rows based on another table. This table might exist in the same database or not. We can use the table lookup method or SQL join to delete these rows.
DELETE Syntax The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!
SQLite DELETE Query is used to delete the existing records from a table. You can use WHERE clause with DELETE query to delete the selected rows, otherwise all the records would be deleted.
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)
149
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); If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
