Multiple Rows Delete in a single table

How to do?

[web_india]

Hi,

I am trying my hands at mysql and am pretty new to it so please bear with me if it is a simple thing for you. :)

I'll try to explain what I am trying to do here.

I have a single table with thousands of rows in it. These rows have a parent child relationship between them like this :

Parent Rows - about 100 (for example)
child Rows - about 1000 (child rows of all the parent rows)
Sub-child rows - about 2000
sub-sub child rows - about 2500

Now, what I am trying to achieve is to delete all the parent rows (except one) AND to delete all the child, sub-child and sub-sub child rows associated with it for the 99 parent rows that I don't want. this would in fact leave me with only one parent row and few of its child, sub-child and sub-child rows

I know that I can delete a row by this command

DELETE FROM `table` WHERE `X` = 'abc'

but what do I need to do to do multiple deletes as mentioned above?

Thanks in advance for your help. :)

[tomda]

This is how I would do it.

1/ Parent ID
2/ Do a query and create an array called Child_array (child ids for all parents, except for the one above)
3/ Do queries using Child_array and create another array called Subchild_array (sub-child ids having Child_array as parent)
4/ Do a query using Subchild_array and create another array called Subsubchild_array (sub-sub-child ids having Subchild_array as parent)

5/ Compile Child_array, Subchild_array and Subsubchild_array

6/ Loop in the array and do your delete query (1 query per id)

My 2cents, hope it helps

[web_india]

tomda, thanks for your prompt reply.
I can follow the logic of the above posts. it is appreciated :)
but as you can see I am bit new to all this and don't know much about arrays. is there a way to do this in phpmyadmin?
or if not, can you give me some pointers from where I can learn about arrays.

I searched a lot on mysql DELETE syntax but I could only find references to WHERE statements as a condition (but couldn't really find how to do the above)

[aspdaddy]

Does the version used not support delete cascades like this:

ALTER TABLE [Parent] ADD
CONSTRAINT [ParentChildLink] FOREIGN KEY
([ChildID]
) REFERENCES [Child] (
[ID]
) ON DELETE CASCADE

If it does just let the database handle it. The script way will always screw up your database one day and the nested delete statments can slow down or hang the server.

[edited by: aspdaddy at 1:34 pm (utc) on April 5, 2006]

[tomda]

I am not good in writing complex Mysql queries, so I can't help you to do the above with one or two queries or using Myadmin. But I can give you the basic to do what I suggest above using PHP and arrays.

Note that this method require numerous queries.

// PARENT TO KEEP
// **************
$parent_to_keep = "2";

// CREATE CHILD ARRAY
// ******************
$child_array=array();
$sql= "select id from table where parent!= $parent_to_keep";
$res=mysql_query($sql) or die ("Fail child_array");
while($pres=mysql_fetch_row($res)) {array_push($child_array,$pres[0]);}
// COUNT NUMBER OF ELEMENTS IN ARRAY
$nb_child_array=count($child_array);
// SHOW CHILD ARRAY
print_r ($child_array);

// CREATE SUBCHILD ARRAY
// *********************
$subchild_array=array();
// LOOP THROUGH CHILD_ARRAY
for($i="0"; $i<$nb_child_array; $i++) {
// QUERY FOR EACH CHILD_ARRAY ELEMENT
$sql= "select id from table where parent = $child_array[$i]";
$res=mysql_query($sql) or die ("Fail subchild_array");
while($pres=mysql_fetch_row($res)) {array_push($subchild_array, $pres[0]);}}
// COUNT NUMBER OF ELEMENTS IN ARRAY
$nb_subchild_array=count($subchild_array);
// SHOW CHILD ARRAY
print_r ($subchild_array);

// CREATE SUBSUBCHILD ARRAY - SAME AS ABOVE
// ************************
$subsubchild_array=array();
// LOOP THROUGH CHILD_ARRAY
for($i="0"; $i<$nb_subchild_array; $i++) {
// QUERY FOR EACH SUBCHILD_ARRAY ELEMENT
$sql= "select id from table where parent = $subchild_array[$i]";
$res=mysql_query($sql) or die ("Fail subsubchild_array");
while($pres=mysql_fetch_row($res)) {array_push($subsubchild_array, $pres[0]);}}
// COUNT NUMBER OF ELEMENTS IN ARRAY
$nb_subsubchild_array=count($subsubchild_array);
// SHOW CHILD ARRAY
print_r ($subsubchild_array);

/* AT THIS POINT, YOU SHOULD HAVE THREE ARRAYS - MAKE SURE IT IS CORRECT BEFORE DELETING YOUR ROWS FROM YOUR DATABASE. PLEASE DO A BACKUP IN CASE YOU DO SOMETHING WRONG
*/

/* REMOVE THIS TO DELETE ROWS

// DELETE CHILD_ARRAY
for($i="0"; $i<$nb_child_array; $i++) {
$sql="DELETE FROM `table` WHERE id = '$child_array[$i]' LIMIT 1;
$res=mysql_query($sql) or die ("Error"); }

// DELETE SUBCHILD_ARRAY
for($i="0"; $i<$nb_subchild_array; $i++) {
$sql="DELETE FROM `table` WHERE id = '$subchild_array[$i]' LIMIT 1;
$res=mysql_query($sql) or die ("Error"); }

// DELETE SUBSUBCHILD_ARRAY
for($i="0"; $i<$nb_subsubchild_array; $i++) {
$sql="DELETE FROM `table` WHERE id = '$subsubchild_array[$i]' LIMIT 1;
$res=mysql_query($sql) or die ("Error"); }

*/

Again, not tested but it should work.
Do a test and report back

Tomda

[tomda]

Oups, as Aspdaddy said, try his method before testing mine because you never know what a script will output, especially if you are new to arrays.

[edited by: tomda at 1:40 pm (utc) on April 5, 2006]

[web_india]

thanks tomda and aspdaddy.

aspdaddy, I think CASCADE is the statement I needed :)
I'll give it a try and report back . Thanks

[web_india]

aspdaddy, it seems REFERENCES works only with InnoDB Storage engines, is it true?
Mine is MyISAM.

I've tried the option you gave above but I keep getting syntax errors

[web_india]

I tried converting it to InnoDB type but it still couldn't work :(