delete from 2 table in sql that have a foreign key in other tables

Question

I want to delete (in one statement) rows from table activities [eventid is PK] and bookings [studentid is FK and eventid is FK]. I tried this :

delete bo from bookings as bo inner join activities as ac on ac.eventid=bo.eventid where bo.eventid=3

but it deletes row only in bookings.

what do I have to change?

You need `cascade` delete. Check --> https://stackoverflow.com/questions/2914936/mysql-foreign-key-constraints-cascade-delete — Digvijay S, Apr 29 '20 at 14:21

score 0 · Accepted Answer · answered Apr 29 '20 at 14:19

You need to reference all the tables you want to delete from:

delete bo, ac
    from bookings bo inner join
         activities ac
         on ac.eventid = bo.eventid
    where bo.eventid = 3;

This syntax is rather clearly explained in the documentation.

Typically, multi-table deletes can be replaced with cascading delete foreign key references. You might be able to handle this just by properly defining your data model.

delete from 2 table in sql that have a foreign key in other tables

1 Answers1