CODEWITHSUNDEEP
sqliteinner-joindelete-row
user1992697
user1992697

Reputation: 315

Deleting rows from two tables using inner join SQLITE

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

Upvotes: 4

Views: 8711

Answers (1)

CL.
CL.

Reputation: 180020

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;

Upvotes: 7

Related Questions