Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
For many ideas about hierarchical queries in MySQL, see here: Find highest level of a hierarchical field: with vs without CTEs –  ypercube Sep 19 '13 at 12:08
    
Also this one: mysql hierarchical queries –  ypercube Sep 19 '13 at 12:11

1 Answer 1

up vote 1 down vote accepted

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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