Hi Falcon ,
In Mysql have a multiple delete is available using this we can delete records from multiple table.
consider the table 'employee' and 'dept'. we need to delete department 20 from both tables use the following code to delete.
Code:
mysql> SELECT * FROM employee;
+----+---------+--------+---------+---------+---------------------+
| id | name | salary | address | dept_id | join_date |
+----+---------+--------+---------+---------+---------------------+
| 1 | kumar | 10000 | chennai | 10 | 2007-01-14 00:00:00 |
| 2 | ramu | 10000 | chennai | 20 | 2007-04-24 00:00:00 |
| 3 | arun | 20000 | chennai | 10 | 2007-02-14 00:00:00 |
| 4 | karthik | 30000 | chennai | 40 | 2007-07-19 00:00:00 |
| 5 | raja | 20000 | chennai | 10 | 2007-06-29 00:00:00 |
| 6 | kamal | 40000 | chennai | 30 | 2007-08-14 00:00:00 |
| 7 | mani | 50000 | chennai | 20 | 0000-00-00 00:00:00 |
| 8 | sunder | 60000 | chennai | 30 | 0000-00-00 00:00:00 |
+----+---------+--------+---------+--------+---------+------------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM dept;
+---------+---------+
| dept_id | name |
+---------+---------+
| 10 | DB |
| 20 | php |
| 30 | flash |
| 40 | testing |
| 50 | admin |
+---------+---------+
5 rows in set (0.00 sec)
mysql> DELETE FROM dept,employee USING dept,employee WHERE dept.dept_id=20 AND employee.dept_id=dept.dept_id;
Query OK, 3 rows affected (0.00 sec)
mysql> SELECT * FROM employee;
+----+---------+--------+---------+---------+---------------------+
| id | name | salary | address | dept_id | join_date |
+----+---------+--------+---------+---------+---------------------+
| 1 | kumar | 10000 | chennai | 10 | 2007-01-14 00:00:00 |
| 3 | arun | 20000 | chennai | 10 | 2007-02-14 00:00:00 |
| 4 | karthik | 30000 | chennai | 40 | 2007-07-19 00:00:00 |
| 5 | raja | 20000 | chennai | 10 | 2007-06-29 00:00:00 |
| 6 | kamal | 40000 | chennai | 30 | 2007-08-14 00:00:00 |
| 8 | sunder | 60000 | chennai | 30 | 0000-00-00 00:00:00 |
+----+---------+--------+---------+--------+---------+------------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM dept;
+---------+---------+
| dept_id | name |
+---------+---------+
| 10 | DB |
| 30 | flash |
| 40 | testing |
| 50 | admin |
+---------+---------+
4 rows in set (0.00 sec)
In this delete statement FROM Class table only will delete, USING class table for support.