how to delete records from four tables in mysql?

Question

mysql> desc courses;
+--------------------+---------------------------+------+-----+---------+----------------+
| Field              | Type                      | Null | Key | Default | Extra          |
+--------------------+---------------------------+------+-----+---------+----------------+
| course_id          | int(11)                   | NO   | PRI | NULL    | auto_increment |
| course_name        | varchar(50)               | NO   | UNI | NULL    |                |
| course_description | text                      | NO   |     | NULL    |                |
| course_added       | datetime                  | NO   |     | NULL    |                |
| status             | enum('Active','Inactive') | NO   |     | Active  |                |
+--------------------+---------------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> desc papers;
+-------------------+---------------------------+------+-----+---------+----------------+
| Field             | Type                      | Null | Key | Default | Extra          |
+-------------------+---------------------------+------+-----+---------+----------------+
| paper_id          | int(11)                   | NO   | PRI | NULL    | auto_increment |
| course_id         | int(11)                   | NO   |     | NULL    |                |
| paper_name        | varchar(50)               | NO   |     | NULL    |                |
| paper_description | text                      | NO   |     | NULL    |                |
| paper_added       | datetime                  | NO   |     | NULL    |                |
| status            | enum('Active','Inactive') | NO   |     | Active  |                |
+-------------------+---------------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> desc question_sets;
+-----------------+---------------------------+------+-----+---------+----------------+
| Field           | Type                      | Null | Key | Default | Extra          |
+-----------------+---------------------------+------+-----+---------+----------------+
| set_id          | int(11)                   | NO   | PRI | NULL    | auto_increment |
| paper_id        | int(11)                   | NO   |     | NULL    |                |
| set_name        | varchar(100)              | NO   |     | NULL    |                |
| set_description | text                      | NO   |     | NULL    |                |
| set_maxtime     | time                      | NO   |     | NULL    |                |
| created_date    | datetime                  | NO   |     | NULL    |                |
| status          | enum('Active','Inactive') | NO   |     | Active  |                |
+-----------------+---------------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

mysql> desc questions;
+---------------+---------------------------------------------------+------+-----+---------+----------------+
| Field         | Type                                              | Null | Key | Default | Extra          |
+---------------+---------------------------------------------------+------+-----+---------+----------------+
| question_id   | int(11)                                           | NO   | PRI | NULL    | auto_increment |
| set_id        | int(11)                                           | NO   |     | NULL    |                |
| question_text | varchar(50)                                       | NO   |     | NULL    |                |
| option_1      | varchar(50)                                       | NO   |     | NULL    |                |
| option_2      | varchar(50)                                       | NO   |     | NULL    |                |
| option_3      | varchar(50)                                       | NO   |     | NULL    |                |
| option_4      | varchar(50)                                       | NO   |     | NULL    |                |
| answer        | enum('option_1','option_2','option_3','option_4') | NO   |     | NULL    |                |
| status        | enum('Active','Inactive')                         | NO   |     | Active  |                |
+---------------+---------------------------------------------------+------+-----+---------+----------------+

These are the four tables the table courses is connected papers table papers is connected to question_sets and question_set is connected to questions if i delete the course_id=1 in table "course" the row corresponds to course_id in table papers should get deleted and the row corresponds to the paper_id in table question_sets should get deleted and the row corresponds to the question_id in table questions should get delete i have tired the following query it deletes the records from the papers table is it possible to perform delete operation in all table single query??

delete from papers where course_id IN (select course_id from courses where course_id=7);

note:the above query deletes the row from paper table

is it possible to perform delete operation for all table in single query? — Arun Kumaresh, Feb 04 '16 at 07:22
Similar question: http://stackoverflow.com/questions/2914936/mysql-foreign-key-constraints-cascade-delete — newman, Feb 04 '16 at 07:34

score 1 · Answer 1 · answered Feb 04 '16 at 08:19

1

Why do you want to delete from four tables in a single query? What's wrong with four delete statements? If you need to make sure all the deletions happen atomically, then wrap the four deletes in a transaction.

answered Feb 04 '16 at 08:19

Ron DeSantis

855
7
5

im going to perform this delete operation in php if i use four delete statement its a long process – Arun Kumaresh Feb 04 '16 at 08:50

Hamidreza Bavafa · Answer 2 · 2016-02-04T08:11:30.657

0

DELETE FROM courses, papers, question_sets , questions
USING courses JOIN papers JOIN question_sets JOIN questions
WHERE courses.course_id= 1
    AND papers.course_id= 1
    AND question_sets.paper_id=1
    AND questions.set_id=1

edited Feb 04 '16 at 08:11

answered Feb 04 '16 at 07:33

Hamidreza Bavafa

18
4

displaying this error #1066 - Not unique table/alias: 'papers' – Arun Kumaresh Feb 04 '16 at 07:41
For example:i want a row from table courses delete and filde course_id the table courses is foreign key at table papers and filde paper_id the table papers is a foreign key at table question_sets question_sets.paper_id=papers.paper_id and filde set_id the table question_sets is a foreign key at table questions questions.set_id=question_sets.set_id – Hamidreza Bavafa Feb 04 '16 at 08:52

score -1 · Answer 3 · answered Feb 04 '16 at 07:32

-1

You can Disable foreign key check by using below query then try to delete or drop table

SET FOREIGN_KEY_CHECKS = 0;
drop table if exists courses;
drop table if exists papers;
drop table if exists question_sets;
SET FOREIGN_KEY_CHECKS = 1;

answered Feb 04 '16 at 07:32

Dipak Kumar

27
1
5

Asked about delete records from tables, not about drop it. – newman Feb 04 '16 at 07:33

how to delete records from four tables in mysql?

3 Answers3