Column Update

Question

I have this table schema and data, I have no idea how I can update all parentid 7.5 and 1 if there is a new member added in parentid 7, for example, recently added - 10. Then all his parents will get into the tree ( Note: up to 10 parents, only starting parent 7, in order to climb in the tree, you can update the quantity there) 7.5 and 1 will add the quantity up 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 adding a 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 does not have a child, and then a new one is added, the parent cannot receive 500, or the update cannot be made in the number of parents.

EDIT last problem

, , . , .., , , , 9 10 , '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

.

.

Answer 1

, ,

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 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;
    END WHILE;
end;
    SELECT s_str;
END;

, .

, .

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 m_mem = par_id;
      IF cnt = 2 OR cntr > 1 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;
      set cntr = cntr+1;
    END WHILE;
end;
    SELECT s_str;
END;  

Answer 2

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;

inserting a row sp_update_amt call sp_update_amt(inserted_memberid); .

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;  

Answer 3

, , mysql . .

: fooobar.com/questions/216176/...