Delete rows from several tables in one query

What is the proper开发者_运维问答 way to delete rows from several tables in one query?

The reason I ask is because I am doing this with PHP. If I use multiple queries to delete from each table one at a time, PHP has to make multiple trips to the database. Will there be any effect on performance if I used this method?

I am aware of the ON DELETE CASCADE option, but this does not work on every storage engine. Also, there may be situations where I do not want to remove all of the records from the child tables when I delete the parent record.

DELETE
  t1, t2
FROM
  table1 AS t1 INNER JOIN table2 AS t2
ON
  joinCondition
WHERE
  whereCondition

As usual with DELETE queries: be very careful

More details here: http://dev.mysql.com/doc/refman/5.5/en/delete.html

If you don't know the answer to this question, then you shouldn't be trying to support numerous RDMS's for your application. To put bluntly. The CASCADE option is available in like every relational db that matters. Also, you should consider looking at how to store hierarchical data, to delete child records.

For example, if you were trying to delete all "files" in a "folder" when using Nested Set Model, it would simply be a matter of

DELETE from files where id > :lft and id < :rgt

But, in any case, you can still delete from multiple tables, by using JOIN deletes. However, this is not support by a lot of RDMS, so if you are worried about using cascade, then you are never going to be able to use join deletes accross every database, even if you use a DBAL.

The Answer

• Use a DBAL, such as Doctrine DBAL (not the ORM), and use Cascades where supported.
• Pick a single database, and develop with what you know on that.

I was also once faced with a similar problem. My solution was to write my own min-recusive query. Just one query and it does the rest for you. Here is how it goes:

//main function

function factory($db){ $table=array('table1', 'table2', 'table3'...); //mine went all the way to table 12
for($i=0; $i<''sizeof($table); $i++){
delete($db, $table[$i]);
} }

//delete function.. could as well setit up withn the factory function but 4 undestanging sake

delete($db, $table){

$sql='delete from '.$table; if($db->query($sql)){ echo ucfirst($table).' delete completed 100%';

}

}

Thats all... to make work for non-predefined insert array, crete a varible to hold the array size from your data entry page and change the delete to 'insert into table' prepare statement and execute. Hope it has helped you in some way