1

I have a table with internal parents as

id    parent    parent_level
1     NULL      1
2     1         2
3     1         2
4     3         3

where parent has a FK to id.

How can I update the table to assign the values of parent_level, according to the parent, and parent of parent of each row.

2

1 Answer 1

2

I would suggest to create stored function for that.

Table definition I've used for debugging:

DROP TABLE IF EXISTS `test`;

CREATE TABLE IF NOT EXISTS `test`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
parent INT NULL,
parent_level INT DEFAULT 0
)ENGINE = InnoDB;

START TRANSACTION;

INSERT INTO `test`
    (`id`, `parent`, `parent_level`)
VALUES
    (1, NULL, 0),
    (2, 1, 0),
    (3, 1, 0),
    (4, 3, 0);

COMMIT;

Now, create a stored function:

DELIMITER $$

DROP FUNCTION IF EXISTS `getLevel`$$ -- drop if exists

CREATE FUNCTION `getLevel`(`anId` INT) -- argument: current row id
RETURNS INT                            -- returns int
NOT DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE `aResult` INT DEFAULT 1;       -- result buffer, default value = 1
DECLARE `aParent` INT;                 -- buffer for parent ids

-- select first parent of this row:
SET `aParent` = (SELECT `parent` FROM `test` WHERE `id` = `anId`);

-- while parent id is set:
WHILE NOT `aParent` IS NULL DO

    -- increase result:
    SET `aResult` = `aResult` + 1;

    -- select next parent:
    SET `aParent` = (SELECT `parent` FROM `test` WHERE `id` = `aParent`);

END WHILE;

-- return result
RETURN `aResult`;
END$$

DELIMITER ;

Now, how to update:

UPDATE
    `test`
SET
    `parent_level` = `getLevel`(`id`)
WHERE           -- if safe mode is on
    `id` > 0;   -- I have to use primary key column in update or it will fail

SELECT
    *
FROM
    `test`;

Results:

1   NULL    1
2   1       2
3   1       2
4   3       3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.