MySql Update Row and it's "Descendants"

Question

I'm having trouble figuring out how to update the descendants/children of a row. Example table, named test

+----+--------+------+
| Id | Parent | Val  |
+----+--------+------+
|  1 |      0 |      |
|  2 |      1 |      |
|  3 |      1 |      |
|  4 |      1 |      |
|  5 |      0 |      |
|  6 |      5 |      |
|  7 |      6 |      |
|  8 |      6 |      |
|  9 |      0 |      |
+----+--------+------+

What I'd like to have done is, when Val is set to something, update every row that is related to that row. For example, If I ran

UPDATE test SET Val=1 WHERE Id=5;

I want Val in the rows where the Id is 6, 7, and 8, to also be 1.

The best thing I could come up with was

UPDATE test t1 
    JOIN test t2 ON t1.Id = t2.Parent 
    JOIN test t3 ON t2.Id = t3.Parent 
SET t1.Val=1, t2.Val=1, t3.Val=1 WHERE t1.Id=5;

+----+--------+------+
| Id | Parent | Val  |
+----+--------+------+
|  1 |      0 |      |
|  2 |      1 |      |
|  3 |      1 |      |
|  4 |      1 |      |
|  5 |      0 |   1  |
|  6 |      5 |   1  |
|  7 |      6 |   1  |
|  8 |      6 |   1  |
|  9 |      0 |      |
+----+--------+------+

This gives me what I want, but I'm afraid it's poor practice and it doesn't account for a variable depth. What can I do here? I thought a trigger may have been the answer, but that didn't seem to be possible. I got the error "Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger"

Answer 1

It's pretty awkward to handle this kind of case with MySQL, because MySQL doesn't support recursive queries.

I solve this problem by storing the hierarchy as a transitive closure, instead of the "parent_id" style you're using. See my answer to What is the most efficient/elegant way to parse a flat table into a tree?

Then you can update all descendants of a given node in the heirarchy:

UPDATE test JOIN testclosure AS c ON test.id = c.descendant
SET test.val = 1
WHERE c.ancestor = 5;