Delete records from multiple table at a time

Falcon · #1 (**permalink**) 03-28-2008, 05:07 AM

Hi Buddies

We can delete records from multiple table in a single query? Are is there any function We used to delete record from multiple table..

Thanks
Falcon

kumaresan · #2 (**permalink**) 03-28-2008, 09:06 PM

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.

Jeyaseelansarc · #3 (**permalink**) 03-28-2008, 09:19 PM

hi,
This really works for me too.
Can v have any other method to delete multiple records from multiple tables at a time?

Falcon · #4 (**permalink**) 03-28-2008, 11:20 PM

Hi Jey

I have another on format to delete records from multiple tables at the same time

Code:

mysql> DELETE dep.*,emp.* FROM dept as dep,employee as emo
WHERE  dep.dept_id=20 AND emp.dept_id=dep.dept_id;

Regards
Falcon

Jeyaseelansarc · #5 (**permalink**) 04-02-2008, 05:27 AM

Hi,
Thanks for the reply.
can we use truncate instead of Delete from the above query?

kumaresan · #6 (**permalink**) 04-02-2008, 05:36 AM

hi jey,

There is no way to use truncate instead of delete from above query because truncate is only can truncate all rows.

Jeyaseelansarc · #7 (**permalink**) 04-03-2008, 03:26 AM

hi,
is it possible to delete single row using truncate?

Murali · #8 (**permalink**) 04-07-2008, 12:12 AM

Please Understand the Purpose of TRUNCATE.
TRUNCATE will not work on row by row,it applies for all the records in the specified table.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Insert Multiple records using single Insert Statement	vadivelanshanmugam	Database Support	0	03-04-2008 09:26 PM
What is the difference between DELETE TABLE and TRUNCATE TABLE commands in SQL Server	oxygen	Database Support	6	11-23-2007 05:17 AM
How to delete a row from parent table that has relationship with the child tables?	$enthil	Database Support	5	08-14-2007 08:04 AM
How can we delete master table record	vadivelanvaidyanathan	Database Support	2	08-08-2007 02:29 AM
How to remove duplicate records from a table? with out using distinct key?	KiruthikaSambandam	Database Support	6	08-06-2007 09:16 PM