CODEWITHSUNDEEP
mysql
Nithin Gangadharan
Nithin Gangadharan

Reputation: 527

WHILE Loop with insert not working as expected

I am try to find all child and sub-child of a given parent, but not getting desired output if I use WHILE loop. Surprisingly, if I execute the scripts manually, it works as expected. Please have a look at below script.

DROP TABLE IF EXISTS test;
CREATE TABLE test (id INT, parent_id INT);
INSERT INTO test VALUES (1,NULL);
INSERT INTO test VALUES (2,1);
INSERT INTO test VALUES (3,2);
INSERT INTO test VALUES (4,2);
INSERT INTO test VALUES (5,3);
INSERT INTO test VALUES (6,4);
INSERT INTO test VALUES (7,2);
INSERT INTO test VALUES (8,4);
INSERT INTO test VALUES (9,1);
INSERT INTO test VALUES (10,1);
COMMIT; 

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `get_parent_child_hierarchy`$$

CREATE PROCEDURE `get_parent_child_hierarchy`(Parent_ID INT)
BEGIN

DECLARE `rowcount` INT DEFAULT 1;

DROP TABLE IF EXISTS Temp;

CREATE TABLE Temp(ParentID INT, ChildID INT, Stage INT);


INSERT INTO Temp(ParentID, ChildID, Stage)
SELECT Parent_ID, Parent_ID, 2;

WHILE rowcount > 0 DO

    UPDATE Temp
    SET Stage = 1
    WHERE Stage = 2;
    COMMIT;

    INSERT INTO Temp(ParentID, ChildID, Stage)
    SELECT Parent_ID, id, 2
    FROM Test T
    WHERE Parent_id IN (SELECT ChildID
                        FROM Temp 
                        WHERE Stage = 1
                        AND Temp.ChildID <> T.id);


    UPDATE Temp
    SET Stage = 0
    WHERE Stage = 1;
    COMMIT;

    SET rowcount = (SELECT COUNT(ChildID)
                    FROM Temp
                    WHERE Stage = 2);

END WHILE;


SELECT *
FROM Temp
WHERE Parent_ID <> ChildID;

DROP TABLE IF EXISTS Temp;

END$$

DELIMITER ;

Input: 2

Expected Output:

ParentID | ChildID | Stage
---------|---------|------
    2    |    3    |   0
    2    |    4    |   0
    2    |    5    |   0
    2    |    6    |   0
    2    |    7    |   0
    2    |    8    |   0

But unfortunately, when the run the script using WHILE loop, I get all the rows in the input.

Any help is appreciated.

I agree this is moreover specific problem than issue with technical stuff. But I wonder if there is any issue with MySQL, when you use WHILE loop and INSERT.

Upvotes: 0

Views: 245

Answers (1)

Blank
Blank

Reputation: 12378

You defined input parameter's name as Parent_ID which is a column in table test, this is reason that you did not get what you expected.

Try following;)

CREATE PROCEDURE `get_parent_child_hierarchy`(pid INT)
BEGIN

DECLARE `rowcount` INT DEFAULT 1;

DROP TABLE IF EXISTS Temp;

CREATE TABLE Temp(ParentID INT, ChildID INT, Stage INT);


INSERT INTO Temp(ParentID, ChildID, Stage)
SELECT pid, pid, 2;

WHILE rowcount > 0 DO

    UPDATE Temp
    SET Stage = 1
    WHERE Stage = 2;
    COMMIT;

    INSERT INTO Temp(ParentID, ChildID, Stage)
    SELECT pid, id, 2
    FROM test T
    WHERE parent_id IN (SELECT ChildID
                        FROM Temp 
                        WHERE Stage = 1
                        AND Temp.ChildID <> T.id);


    UPDATE Temp
    SET Stage = 0
    WHERE Stage = 1;
    COMMIT;

    SET rowcount = (SELECT COUNT(ChildID)
                    FROM Temp
                    WHERE Stage = 2);

END WHILE;


SELECT *
FROM Temp
WHERE pid <> ChildID;

DROP TABLE IF EXISTS Temp;

END

Upvotes: 1

Related Questions