Reputation: 1141
Updating column amount
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.
Upvotes: 4
Views: 374
Answers (3)
Reputation: 1342
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
Upvotes: 4
Reputation: 1342
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;
Upvotes: 2
Reputation: 1172
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
Upvotes: 1