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!

does the sessions table reference itself with the session_id key? — Adriano Varoli Piazza
– Adriano Varoli Piazza, Commented Feb 28, 2010 at 19:13

OMG Ponies · Accepted Answer · 2010-02-28 23:07:08Z

4

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:

BOOKINGS

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

SESSIONS

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

COURSES

DELETE FROM COURSES
 WHERE course_id = ?

edited Feb 28, 2010 at 23:07

answered Feb 28, 2010 at 19:14

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

Adriano Varoli Piazza Over a year ago

does my proposed solution have any drawbacks apart from depending on using the InnoDB engine? Also, it's _id for the keys.

OMG Ponies Over a year ago

@Adriano Varoli Piazza: DELETE ON CASCADE isn't a recommended practice, because someone else wouldn't necessarily know it is in place & end up wiping out data they shouldn't have. While it seems like more work, being explicit about what is being done is the best approach.

Fred Over a year ago

Hi There, Thanks for the response, I am attempting to use the first multi delete method: DELETE FROM bookings, sessions, courses USING bookings JOIN sessions JOIN courses WHERE bookings.session_id=sessions.session_id AND sessions.course_id=courses.course_id AND courses.course_id="13" This doesn't seem to be working - is my syntax correct? -Thanks!

bart Over a year ago

If you don't use transactions, don't use a sequence of related actions.

OMG Ponies Over a year ago

@bart: Yes, which is why I mentioned "stored procedure".

|

Adriano Varoli Piazza · Accepted Answer · 2010-02-28 19:03:12Z

3

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

answered Feb 28, 2010 at 19:03

Adriano Varoli Piazza

7,4395 gold badges42 silver badges50 bronze badges

Collectives™ on Stack Overflow

MySQL delete from 3 tables

2 Answers 2

14 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

14 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related