homepage Welcome to WebmasterWorld Guest from 54.161.155.142
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Multiple Rows Delete in a single table
How to do?
web_india




msg:1580563
 12:38 pm on Apr 5, 2006 (gmt 0)

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




msg:1580564
 12:56 pm on Apr 5, 2006 (gmt 0)

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




msg:1580565
 1:03 pm on Apr 5, 2006 (gmt 0)

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




msg:1580566
 1:32 pm on Apr 5, 2006 (gmt 0)

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




msg:1580567
 1:33 pm on Apr 5, 2006 (gmt 0)

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




msg:1580568
 1:35 pm on Apr 5, 2006 (gmt 0)

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




msg:1580569
 1:39 pm on Apr 5, 2006 (gmt 0)

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




msg:1580570
 5:08 pm on Apr 5, 2006 (gmt 0)

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




msg:1580571
 1:11 am on Apr 6, 2006 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved