MySQL delete from 3 tables

Question

Hi There,

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

I think the best way would be to configure the tables adding proper foreign keys (you'll have to use InnoDB for this to actually work in mysql) and setting the behavior of the FKs to 'ON DELETE CASCADE'. This way, when you delete something from the courses table, the related bookings and sessions will be deleted automatically.

Some linksies:

• MySQL DELETE, with possible gotchas about this.
• Foreign Keys examples

Answer 2

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 = ?