MySQL removes from three tables

Question

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

session_id orders

sessions session id course_id

course_id courses

I want to create a query to delete the entire date related to one course (course_id). For example, if I need to delete course_id = 10, I would like any sessions with the identifier course_id = 10 to be deleted, in addition, you must also delete any orders associated with any of these sessions.

Is it possible? What is the best way to approach him? (I am writing this in PHP.)

Any help is much appreciated!

Answer 1

MySQL :

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

:

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

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

• DELETE FROM COURSES
   WHERE course_id = ?
  

Answer 2

, - , ( InnoDB , mysql) FK "ON DELETE CASCADE". , - , .

:

• MySQL DELETE, .