CODEWITHSUNDEEP
phpmysqlsqlsql-delete
Masoud Aghaei
Masoud Aghaei

Reputation: 1221

delete several related rows from diffrent tables

I have a db like this

table 1 : area
+------------+
| id | name  |
+------------+
| 1  | area1 |
+------------+

table 2 : area_members
+------------------------+
| rel_area  | rel_school |
+------------------------+

table 3 : school
+------------+
| id  | name |
+------------+

table 4 : school_members
+---------------------------+
| rel_school  | rel_student |
+---------------------------+

table 5 : student
+------------+
| id  | name |
+------------+

In this table any area has several schools and any school has several students. I want to delete anythings which related to 'area1' (area , schools , students ) from all of these 5 tables by deleting area1 .

I have tried some JOIN and UNION queries but I couldn't fix it . can any one help me , please ? :)

Upvotes: 1

Views: 72

Answers (2)

mostafa khansa
mostafa khansa

Reputation: 302

when defining foreign key constraint you can specify whether deleted record affect other records that is related to it with the foreign key there is an option you can specify when declaring the foreign key : on delete cascade. suppose table B belong to table A . if you specify on delete cascade : this will cause records in table B to be deleted if they are related to record in table A, when this record in table A is deleted

Upvotes: 0

John Woo
John Woo

Reputation: 263843

You could simply join the tables and the best part is, MySQL allows you to delete multiple tables in just one query.

DELETE  a, b, c, d, e
FROM    area a
        INNER JOIN are_members b
            ON a.ID = b.rel_area
        INNER JOIN school c
            ON b.rel_school = c.ID
        INNER JOIN school_members d
            ON c.ID = d.rel_school
        INNER JOIN student e
            ON d.rel_student = e.ID
WHERE   a.Name = 'area1'

PS: please backup your database before executing the statement above

Upvotes: 2

Related Questions