MySQL |更新查询以填充子项

I have a table like

我有一张桌子

ID | ParentID |Children| Name | Address
----------------------------------------
1  |  Null    |        | Xyz  | Home
2  |   1      |        | ABC  | Home
3  |   2      |        | DEF  | Home
4  |   3      |        | GHI  | Home
5  |   3      |        | GHI  | Home
6  |   1      |        | GHI  | Home

I need to fill the Children with a query, these are immediate children. For example children for 1 will be 2 and children for 3 will be 4,5

我需要向孩子们填写查询,这些是直接的孩子。例如,1的子项为2,3的子项为4,5

So the table after query should look like

所以查询后的表应该是这样的

ID | ParentID |Children| Name | Address
----------------------------------------
1  |  Null    |  2,6   | Xyz  | Home
2  |   1      |   3    | ABC  | Home
3  |   2      |  4,5   | DEF  | Home
4  |   3      | Null   | GHI  | Home
5  |   3      | Null   | GHI  | Home
6  |   1      | Null   | GHI  | Home

I know I can use group_concat function with update query but not getting the exact query. How can I do this with a MySQL query?

我知道我可以使用group_concat函数和更新查询但不能获得确切的查询。如何使用MySQL查询执行此操作?

1 个解决方案

#1

You can create a subquery which concatenate the ID for its children and join it with the table itself.

您可以创建一个子查询,该子查询连接其子项的ID并将其与表本身连接。

UPDATE  tableName a
        INNER JOIN
        (
            SELECT  a.ID, GROUP_CONCAT(b.ID) Children
            FROM    tableName a
                    INNER JOIN tableName b
                        ON a.ID = b.ParentID
            GROUP   BY a.ID
        ) b ON a.ID = b.ID
SET     a.Children = b.Children