How to delete data from multiple tables in sqlite?

Question

I am using sqlite database to store data. I have three tables: Invoice, InvRow, Invdetails.

Relationsip between the tables are:

Invoice.Id = InvRow.InvId
InvRow.Id = Invdetails.RowId

I need to delete related entries from three tables using a single query. How can I do that? Any help?

Answer 1

SQLite prior to version 3.6.19 (2009 Oct 14) does not support foreign key constraints, but you can use triggers to maintain relational integrity.

Starting with 3.6.19, however, SQLite supports proper foreign key constraints with ON [UPDATE|DELETE] CASCADE clauses, that will do what you want.

Answer 2

Alex B provides a good answer but it didn't work for me. too much work right now.

Unfortunately I had to do the delete in two statements, but it worked out just fine. I have two tables, movies and movie_providers, movie_providers is a join table that tells which provider is showing my movie.

I wanted: DELETE FROM movies, movie_pproviders WHERE movie.id = movie_providers.movie_id AND [my constraint]

but had to do 2 steps

1. DELETE FROM movies WHERE [my constraint]

2. DELETE FROM movie_providers where movie_id NOT IN (select distinct id from movies)

This assumes i had consistency between movies and movie_providers before. If not, I just created consistency.

Worked fine... hope it helps someone. Rajat Banerjee