stored procedures - Updating a column name of a same table which has a parent child relationship using mysql -

i searched lot of doing task found no appropriate solution.

basically scenario is. have user_comment table in there 5 column(id,parent_id,user_comments,is_deleted,modified_datetime). there parent child relationship 1->2,1->3,2->4,2->5,5->7 etc.
now sending id front end , want update column is_deleted 1 , modified_datetime on records on id children , children's of children.

i trying doing using recursive procedure. below code of procedure

     create definer=`root`@`localhost` procedure `user_comments`(      in mode varchar(45),      in comment_id int,      )      begin       declare p_id int default null ;       if(mode = 'delete')           update user_comment set is_deleted = 1, modified_datetime = now()        id = comment_id ;       select id user_comment parent_id = comment_id p_id ;       if p_id not null            set @@global.max_sp_recursion_depth = 255;      set @@session.max_sp_recursion_depth = 255;      call user_comments('delete', p_id);     end if;     end if;     end 

by using procedure give me error of more 1 row. if return select query without giving variable shows me the appropriate results on select query have call procedure recursively based on getting ids of select query.

i need have passed 2 days this.

i used cursor also. below code of cursor

   create definer=`root`@`localhost` procedure `user_comments`(    in mode varchar(45),    in comment_id int,    )    begin    declare p_emp int;    declare nomorerow int;    declare cur_emp cursor  select id user_comment parent_id = comment_id ;    declare continue handler not found set nomorerow = 0;      if(mode = 'delete')          open cur_emp;      looprows: loop        if nomorerow = 0       update user_comment set is_deleted = 1, modified_datetime = now() id = comment_id          close cur_emp;         leave looprows;     end if;     fetch cur_emp p_emp;     update user_comment set is_deleted = 1, modified_datetime = now() id = p_emp ;     set @@global.max_sp_recursion_depth = 255;     set @@session.max_sp_recursion_depth = 255;      call user_comments('delete', p_emp);      end loop;    end if;  end 

after using cursor getting thread error.i don't know how can overcome problem!!!

mysql's documentation on select ... varlist says:

the selected values assigned variables. number of variables must match number of columns. query should return single row. if query returns no rows, warning error code 1329 occurs (no data), , variable values remain unchanged. if query returns multiple rows, error 1172 occurs (result consisted of more 1 row). if possible statement may retrieve multiple rows, can use limit 1 limit result set single row.

since wrote in op comment can parent of many comments, using simple variables cannot solution. should use cursor instead, can store entire resultset.

you loop through records within cursos shown in sample code in above link , call user_comments() in recursive way.

update

if receive

error code: 1436. thread stack overrun

error, can 2 things:

1. increase thread_stack setting in config file , restart mysql server.

2. you can try simplify code use less recursions , therefore less stack space. example, when fetch children cursor, rather calling user_comments() recursively each, can set direct children's status within code , call function recirsively on grand-childrens (if any). can change data structure , use nested set model approach hierarchical structures.

nested set model more complex understand, less resource intensive traverse, more resource intensive maintain.