One line query to delete specific records from multiple tables

mastikhor

On clients machine, currently to delete on trainee record it runs 10 queries to delete records from 10 tables. At the time of running all queries, server shows (104) Connection reset by peer. An error condition occurred while reading data from the network.

I think it because of running 10 queries at a same time. Is there any possibility that through one line of query we can delete record from 10 tables.

I've tried following query

DELETE FROM table1, table2, table3, table4, table5, table6, table7, table8, table9, table10 WHERE empID = 11;

But it gives ' error in query.

Any tip

rincewind456

The manual has the syntax for multiple deletes http://dev.mysql.com/doc/refman/5.0/en/delete.html

Sxooter

Note that if you are using innodb (or just about any database other than MySQL with myisam tables) you can declare your fk/pk relationships such that deletes cascade.

Then you just

delete from mastertable where id=456

and all the child records in the subordinate tables get deleted as well.

mastikhor

Database is mySQL, with PHP and Apache Server.

There is relationship of parent and child between tables. There is parent table "accounts" which has all employees basic information. ID of accounts (accounts.id) is available in child tables table2.empId. So you can say that accounts.id = table2.empID, accounts.id = table3.empID and so on..

Do you think we that relationship I can write one line of delete statement?

Sxooter

Sure, you can delete them in one statement, IF you're using innodb tables and IF you're using foreign keys declared with on delete cascade.

Otherwise, I'm not so sure. I think mysql supports some kind of delete with a join clause, but haven't used it.