MySQL delete from 3 tables
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 appreciate开发者_如何学JAVAd!
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 = ?
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
精彩评论