MySQL delete from 3 tables

Question

I have the following tables, with these keys in my database:

bookings session_id

sessions session_id course_id

courses course_id

I want to create a query to delete all date relating to a single course (course_id). For example if I wanted delete course_id=10, I would want any sessions with course_id=10 to be deleted, in addition any bookings associated with any of these sessions need to be deleted too.

Is this possible? what is the best way to approach it? (I'm writing this in PHP.)

Any help much appreciated!

Answer 1

MySQL supports multi-table deletes:

DELETE FROM BOOKINGS 
 USING BOOKINGS JOIN SESSIONS JOIN COURSES
 WHERE BOOKINGS.session_id = SESSIONS.session_id
   AND SESSIONS.course_id = COURSES.course_id
   AND COURSES.course_id = ?

Another alternative would be to use stored procedure, and process the deletions in proper order:

1. BOOKINGS

   DELETE FROM BOOKINGS
    WHERE EXISTS(SELECT NULL
                   FROM SESSIONS s 
                  WHERE s.session_id = session_id
                    AND s.course_id = ?)
   

2. SESSIONS

   DELETE FROM SESSIONS
    WHERE EXISTS(SELECT NULL
                   FROM COURSES c
                  WHERE c.course_id = course_id
                    AND c.course_id = ?)
   

3. COURSES

   DELETE FROM COURSES
    WHERE course_id = ?