Jet 4.0 Can I delete from multiple tables?

[drewsirrom]

Does anyone know if it is possible to delete from more than one table using a single SQL DELETE statement in MSAccess? I failed with Query Builder using code like:

DELETE Bookings.*, Customers.*
FROM Bookings INNER JOIN Customers ON Bookings.ID = Customers.BookingID
WHERE Customers.ID = 3;

I put in a relation between the two table but it still didn't work - I just get "Could not delete from specified tables".

Thanks in advance to anyone who knows

[exterminator]

Your tables are wrongly built. The customers table should not be having booking's information but the bookings table should be having customer information. That is the join/table relationship should be ON Bookings.CustomerID = Customers.CustomerID and NOT like what you have now that is Bookings.ID = Customers.BookingID.

The problems it seems that you are having is deleting from tables which are related via a PK-FK relationship. In these cases, first the records from the child table is deleted and then from the parent table. That is first the booking information will be deleted for the CustomerID and then the Customers table will be deleted for the CustomerID (that is 3). If you are facing problems doing it so - which I think you will - that means tables are wrongly related (as pointed out by me earlier above). You will have two seperate DELETEs as part of the same TRANSACTION and second DELETE (from customers) will happen only after the first DELETE (from Bookings) is successful.

Also, on a side note, note that data for customers is rarely deleted from databases. The most you will do is either "MOVE" them to a DELETED_CUSTOMERS and DELETED_BOOKINGS table by setting a TRIGGER on Customers and Bookings table or have a flag in both tables as "Active" which is set to false when you delete (which you don't actually do) and otherwise it will remain true by default.

Hope it makes sense.

[davide++]

Hi all.

DELETE command can remove records from only one table, which are selected according to WHERE clause; so a join in a DELETE statement makes no sense.

You can delete records from two related table by only one command if you have defined the relatioship with "DELETE CASCADE" option: when you delete the "record father", Access automatically deletes the related records in child table also.
Personally, I dislike making relationship with this option, because deleting multiple records in this way is based on a "side effect", and I think one must know what is happening when he is deleting data.

I agree with exterminator when he says that delete operation is quite rare (in particular for tables like Customers). Generally, is better to move records in historical tables instead of deleting them; using a flag to mark active record is another solution, but one loses the advantage to get a table with few record, that is easier to manage.