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.

enter image description here

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.

@smn_onrocks,thank you for the reply..It is controlled in registration form before the admin will add 11(new member) he will check first if that member 7 has no left or right child,if already have, then admin will assign parent for the new member which has no child yet. — ashTon, Dec 23 '14 at 07:25
and what about the amount?? I mean when amount is added your amount updation logic is not very clear for me. — smn_onrocks, Dec 23 '14 at 07:29
@smn_onrocks,okay I'll try to explain, when 11 is added in parent 9,since parent 9 has no child yet there is no update could be made.,..this is the requirment if any of the donwlines get paired then his parent will receive 500 go up to the tree (only 10 parent) beyond that 10 parents they cannot receive 500. — ashTon, Dec 23 '14 at 07:35
I think in that case better you write a store proc for implementing the business logic . — smn_onrocks, Dec 23 '14 at 07:49
http://en.wikipedia.org/wiki/Nested_set_model read the details. you will get the idea how to maintain this kind of model. — Nikson Kanti Paul, Dec 23 '14 at 07:49
@NiksonKantiPaul I already read that it's difficult to implement in my registration form I could not let the member put the position left or riht because it bases on link nodes — ashTon, Dec 23 '14 at 07:51
@smn_onrocks,I have no idea on creating sp.that's also my problem — ashTon, Dec 23 '14 at 07:52
@smn_onrocks, do you have any reference on sp that suited to my problem? — ashTon, Dec 23 '14 at 07:55
I will try to solve your scenario but not sure please provide the input parameters you want to pass. — smn_onrocks, Dec 23 '14 at 07:58
the parameter is the memberid because this is what i used in my registration form if i add new member,just put example memberid 10 (a new meberid) added under parent 7..so 7 will receive 500, parent 5 will receive 500(1000) and parent 1 will receive 500 (1500).thank you in advance. — ashTon, Dec 23 '14 at 08:14
@smn_onrocks,can you please not to limit 10 parents go up to tree,just update the amount whoever his parents.so that this could be easy for you.Thank you in advance. — ashTon, Dec 24 '14 at 05:26
I have posted an answer for you plese let me know was it help ful or not — smn_onrocks, Dec 24 '14 at 07:39

smn_onrocks · Accepted Answer · 2015-01-07T12:47:59.403

4

I think this is what you wanted

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;

Try the above code think this will work and solve your problem for details check the link

EDIT AND UPDATED CODE

I think this WOULD solve your requirement.

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;

Check the link for your data and code updated

edited Jan 07 '15 at 12:47

answered Jan 05 '15 at 05:38

smn_onrocks

1,282
1
18
33

thank you for the reply...why is it that it will add immediately the amount? – ashTon Jan 07 '15 at 10:00
First let me know is it working or not giving a up vote as u have accepted the ans so any one facing the same problem could consider the ans then i will explain it – smn_onrocks Jan 07 '15 at 10:08
,...okay I give you my vote now :)...i tried to reset my table and initial 1 as the root.when I add 2 as a child of 1 it update the amount of 1,but it should not update yet because 1 has only one child he did not get paired yet. – ashTon Jan 07 '15 at 11:49
your last post on dec 24,that works fine to me but there is a little problem...here is the scenario.I tried 1 as root then 2 as child of 1,then 3 as child of 1,the amount of 1 is updated it's okay.now I add 4 as child of 2 then add 5 as child of 4 then 6 as child of 5 then 7 as child of 6 then 8 as child of 7 then 9 as child of 7,parent 7 get paired the amount was updated,but what about 6,5 and 4 they are the parent of 7 they should get updated.but the sp only update the 7 amount.this was the problem in your last post dec 24.thank you in advance.and I really appreciate your help. – ashTon Jan 07 '15 at 11:57
,how can i give that bounty to you from jemz? – ashTon Jan 07 '15 at 12:00
I vote again in your post,the answere that i accepted before,i don't know why it remove. – ashTon Jan 07 '15 at 12:04
Please take the second sp as the final one hope this will solve the problem .and let me know dose that solve or not. – smn_onrocks Jan 07 '15 at 12:49
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/68341/discussion-between-ashton-and-smn-onrocks). – ashTon Jan 07 '15 at 12:50

smn_onrocks · Answer 2 · 2015-01-05T05:39:12.920

2

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;

edited Jan 05 '15 at 05:39

answered Dec 24 '14 at 07:25

smn_onrocks

1,282
1
18
33

,It works I tested it thank you so much for helping me,can I ask how did you come up this solution if you don't mind.Thank you in advance – ashTon Dec 24 '14 at 09:28
c my logic was clear what i need to do and belive me this is my first in mysql i never tried this data base before. – smn_onrocks Dec 24 '14 at 10:48
that cntr <=10 is that for parents for updating 10 parentid go to top tree ? – ashTon Dec 26 '14 at 02:57
,can I ask can I remove cntr<=10 in the while loop,if ever they change there mind to just update all his parentid to give 500 ? – ashTon Dec 26 '14 at 04:17
Yes that can be done and the `cntr <=10` is just to restrict the updation in parent level up to 10 levels – smn_onrocks Dec 26 '14 at 05:22
,is it possible if those parents are above 10 level,I will give them 200 amounts.?thank you in advance – ashTon Dec 30 '14 at 07:57
@ashTon yes why not. it is possible you have to modify the code acordingly – smn_onrocks Dec 30 '14 at 08:41
I edit my post,I change now the code accordingly but can you please help me if that is the correct way.thank you in advance. – ashTon Dec 30 '14 at 09:49
1

@ashTon I have upload the updated code please use that one and let me know how it works – smn_onrocks Dec 30 '14 at 10:09
,it works I tested it,I will write you back if I have problem thank you so much again. – ashTon Dec 30 '14 at 10:36
I have problem,if I will add 11 under 9 (left) as child then add 12 under 11(left) as child then add 13 and 14 child under 12,it will only update the 12 amount and the sp will not continue to update .please help me.thank you in advance – ashTon Jan 03 '15 at 09:53
,..I post my latest problem please see my updated problem.I really need your help.Thank you in advance. – ashTon Jan 03 '15 at 17:15
I will be out for few days if you find any problem please mail me on subhankarmitraneogi@gmail.com – smn_onrocks Jan 05 '15 at 05:33

score 1 · Answer 3 · edited May 23 '17 at 12:06

1

You might be able to do this with a recursive common table expression, but mysql does not support them. Therefore just write a stored procedure to do this.

Here is a link to a related question: https://stackoverflow.com/a/8833676/4350148

edited May 23 '17 at 12:06

Community

1
1

answered Dec 23 '14 at 09:58

dan b

1,172
8
20

Updating column amount

3 Answers3