Deleting rows from two tables using inner join SQLITE

sqlite inner-join delete-row
user1992697 picture user1992697 · Feb 10, 2013 · Viewed 7.5k times · Source

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

Answer

CL. picture CL. · Feb 10, 2013

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;

Related questions

Multiple INNER JOIN from the same table

I have a table of metals MetalID integer MetalName text MetalCode text Item table ItemID integer ItemName text ... Metal1 int Ref.-> metals.metalID Metal2 int Ref.-> metals.metalID Metal3 int Ref.-> metals.metalID I …

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How to list the tables in a SQLite database file that was opened with ATTACH?

What SQL can be used to list the tables, and the rows within those tables in an SQLite database file - once I have attached it with the ATTACH command on the SQLite 3 command line tool?

Read More