CREATE DEFINER=`root`@`localhost` PROCEDURE `SampleProc`( name_in INT)
BEGIN
DECLARE exit_loop BOOLEAN;
DECLARE l_name INT;
DECLARE X INT;
DECLARE Y INT;
DECLARE c1id INT;
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT PaperID FROM scrpd_paper_authors WHERE AuthorID=name_in;
DECLARE cur1 CURSOR FOR SELECT AuthorID FROM scrpd_paper_authors WHERE PaperID=l_name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;
OPEN cur;
example_loop: LOOP
FETCH cur INTO l_name;
IF exit_loop THEN
CLOSE cur;
LEAVE example_loop;
END IF;
OPEN cur1;
example_loop1:LOOP
FETCH cur1 INTO c1id;
UPDATE recipes_new SET co_auths = IF(LENGTH(co_auths), CONCAT(co_auths, ',', c1id), c1id) WHERE id = name_in;
IF exit_loop THEN
CLOSE cur1;
LEAVE example_loop1;
END IF;
END LOOP example_loop1;
END LOOP example_loop;
END$$
I made a stored procedure in sqlyog, the problem I ran into is that when I see the results in the table after the update has been done, it is 1,2,3,3, whereas it should be 1,2 , 3 last record is repeated. Any guesses what the problem is?
recipes_new(My current result) scrpd_paper_authors
id co_auths PaperID AuthorID
1 1,1 1 1
2 2,2 2 2
3 3,4,4 3 3
4 3,4,5,6,7,7 3 4
4 5
4 6
4 7
as you can see in recipes_new (My current result), getting extra numbers at the end. My expected result
1
2
3,4
3,4,5,6,7
source
share