How to UPDATE a table for the level of parents in a column?

Question

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.

score 2 · Accepted Answer · answered Sep 21 '13 at 07:03

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

How to UPDATE a table for the level of parents in a column?

1 Answers1