CODEWITHSUNDEEP
sqlite
diana
diana

Reputation: 427

How to delete data from multiple tables in sqlite?

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?

Upvotes: 6

Views: 14876

Answers (2)

rajat banerjee
rajat banerjee

Reputation: 1294

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

DELETE FROM movies WHERE [my constraint]
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.

Upvotes: 4

Alex B
Alex B

Reputation: 84822

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.

Upvotes: 8

Related Questions