Updating column amount

Question

I have this table schema and data,I have no idea on how can I update all parentid 7,5 and 1 if there is new member added under parentid 7 example the newly added is 10.Then all his parentsid go up in the tree (Note: up to 10 parents only starting parentid 7 to go up in the tree can be updated there amount) 7,5 and 1 will be added amount to 500.

CREATE TABLE `mytree` (
    `pid` INT(11) NOT NULL,
    `memd` INT(11) NOT NULL,
    `position` CHAR(1) NOT NULL,
    `amount` DECIMAL(10,2) NOT NULL
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
;



pid          memd     position      amount

0             1                     1000.00

1             5          L          500.00

1             6          R          0.00

5             7          L          0.00

5             8          R          0.00

7             9          L          0.00

Here is my tree.

After added new member 10

pid          memd     position      amount

0             1                     1500.00

1             5          L          1000.00

1             6          R          0.00

5             7          L          500.00

5             8          R          0.00

7             9          L          0.00

7             10         R          0.00

EDIT IF the parent has no child yet then added a new one,the parent cannot recieve 500,or there is no update could be made in amount of parent.

EDIT latest problem

Problem if I have series of one child,then one of the children get paired.,it will only update the amount of his direct parent,the above parent of his parent etc.., did not updated, how can I update those his parent even it has only 1 child,example 9 and 10 should receive also amount because they are the parent of '11'

   pid          memd     position      amount

    0             1                     1500.00

    1             5          L          1000.00

    1             6          R          0.00

    5             7          L          500.00

    5             8          R          0.00

    7             9          L          0.00

    9             10         L          0.00

    10             11         L          0.00

    11             12         L          0.00

    11             13         R          0.00

How can I achieve this.

Thank you in advance.

Answer 1

Here Is your code

create procedure sp_update_amt(IN p_mem  INT)
BEGIN
    declare cnt INT;
    declare par_id INT;
    declare cntr INT;
    declare m_mem INT;
    set cntr=1;
    set par_id = 1;
    set m_mem = p_mem;
    set s_str = '';
proc_label:BEGIN
    WHILE cntr <= 10 and par_id != 0 DO
      SELECT pid INTO par_id FROM mytree WHERE memd=m_mem;
      select count(*) into cnt FROM mytree WHERE pid=par_id;           
      set cntr = cntr+1;
      set m_mem = par_id;
      IF cnt = 2 THEN
        update mytree set amount = amount+500 
        where memd=par_id;
      ELSE
        LEAVE proc_label;
      END IF;
    END WHILE;
end;

END;

After inserting a row call the sp_update_amt by call sp_update_amt(inserted_memberid); that will work. you can chake the entire thing on this Link

EDITED current VERSION

create procedure sp_update_amt(IN p_mem  INT)
BEGIN
    declare cnt INT;
    declare par_id INT;
    declare cntr INT;
    declare m_mem INT;
    declare s_str VARCHAR(512);
    set cntr=1;
    set par_id = 1;
    set m_mem = p_mem;
    set s_str = '';
proc_label:BEGIN
    WHILE par_id != 0 DO
      SELECT pid INTO par_id FROM mytree WHERE memd=m_mem;
      select count(*) into cnt FROM mytree WHERE pid=par_id;
      set s_str = CONCAT(s_str,cnt,cntr,par_id,m_mem,',');      
      set cntr = cntr+1;
      set m_mem = par_id;
      IF cnt = 2 THEN
        IF cntr <=10 THEN
          update mytree set amount = amount+500 
          where memd=par_id;
        ELSE
          update mytree set amount = amount+200 
          where memd=par_id;
        END IF;
      ELSE
        LEAVE proc_label;
      END IF;
    END WHILE;
end;
    SELECT s_str;
END;