2

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.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
recount88
  • 121
  • 1
  • 3
  • 5

3 Answers3

2
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

Mchl
  • 61,444
  • 9
  • 118
  • 120
  • I have heard that deleting from multiple tables at once is not preferred. Is this because the syntax does not allow the use of `LIMIT`? – recount88 Aug 17 '11 at 13:02
  • In general it's easier to make a mistake that will delete much more than you intended. Test your queries well, and you should come to no harm. – Mchl Aug 17 '11 at 13:03
  • Is there any difference in speed if I used multiple queries to delete as opposed to one? – recount88 Aug 17 '11 at 13:12
  • This can't be answered on general basis. It's like SELECT with JOIN. In some cases it can be fast, in others slow, depending on conditions used, datatypes used for comparisons, indexes etc... – Mchl Aug 17 '11 at 13:46
0

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.
Layke
  • 51,422
  • 11
  • 85
  • 111
0

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

AJ Makoni
  • 1