Auto column number based on other column

+--------+---------+-----------+-------+
|   id   | title   | parent_id | root  |
+--------+---------+-----------+-------+
|    1   | Lvl-1   |     0     | null  |
+--------+---------+-----------+----开发者_StackOverflow社区---+
|    2   | Lvl-2   |     0     | null  |
+--------+---------+-----------+-------+
|    3   | Lvl-11  |     1     |   1   |
+--------+---------+-----------+-------+
|    4   | Lvl-12  |     1     |   1   |
+--------+---------+-----------+-------+
|    5   | Lvl-121 |     4     |   1   |
+--------+---------+-----------+-------+

Here is my problem, every time mysql insert new row, i want a value in root point to the top level parent (actually value in root somehow related to value in parent_id ).

Based on the table, row with an id = 5, have a parent_id = 4, and row with id = 4 have parent_id = 1, and row with id = 1 have parent = 0.So this row get root = 1

Basically root value come from cascading parent_id to the top level (till parent_id = 0)

So in summary, what i want is, when new row is insert, Mysql autoamatically assign value for root

So is this can be done?

Thanks in advance

You can do that easily with triggers:

delimiter //
CREATE TRIGGER update_root BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
    SET NEW.root = (SELECT root FROM my_table WHERE id = NEW.parent_id);
END; //
delimiter ;