开发者

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:

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


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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜