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
– Preet Sangha, Commented Feb 10, 2013 at 20:37

CL. · Accepted Answer · 2013-02-10 22:04:12Z

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, 2013 at 22:04

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1992697 Over a year ago

What if you want the user in a program to pick that key value, without you specifying it

CL. Over a year ago

Then you let the user pick the value, then put that value into the query.

Collectives™ on Stack Overflow

Deleting rows from two tables using inner join SQLITE

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related