Deleting rows from two tables using inner join SQLITE

Question

How do you delete a row from two separate tables? I thought it would be possible to do this using an inner join

DELETE a.*, b.* FROM Holiday INNER JOIN Accommodation b on a.LocationID = b.LocationID

Here i try to delete by matching the primary key location in the first table to the location id in the second table. I get an SQL Exception "sqlException near a"

Im doing this in SQLITE, java

enter image description here

Not sure of the exact syntax for multi-table delete but regardless you've forgotten your correlation name of a for Holiday table. `FROM Holiday INNER JOIN` should be `FROM Holiday a INNER JOIN` — Preet Sangha, Feb 10 '13 at 20:37

score 7 · Accepted Answer · answered Feb 10 '13 at 22:04

7

In SQLite, one DELETE command deletes from only one table.

Your query, as written, doesn't actually restrict the records to be deleted, so if you really want to delete all records, you would use this:

DELETE FROM Holiday;
DELETE FROM Accommodation;

If you want to delete one record in the master table and all corresponding records in the child table, you just filter by that key value:

DELETE FROM Holiday       WHERE LocationID = 1;
DELETE FROM Accommodation WHERE LocationID = 1;

answered Feb 10 '13 at 22:04

CL.

173,858
17
217
259

What if you want the user in a program to pick that key value, without you specifying it – user1992697 Feb 10 '13 at 22:32
1

Then you let the user pick the value, then put that value into the query. – CL. Feb 11 '13 at 09:29

Deleting rows from two tables using inner join SQLITE

1 Answers1