sql中根据父节点获取所有的子节点语句

WITH sub_nodes(pk_id) AS (SELECT pk_id FROM IBP_T_Regions WHERE iparentid 56UNION ALLSELECT tn.pk_id FROM IBP_T_Regions tnJOIN IBP_T_Regions s ON tn.iparentid s.pk_id)SELECT * FROM sub_nodes;如果修改指定父节点所有子节点的部门id值如下WITH sub_nodes(pk_id) AS (SELECT pk_id FROM IBP_T_Regions WHERE iparentid 1UNION ALLSELECT tn.pk_id FROM IBP_T_Regions tnJOIN sub_nodes s ON tn.iparentid s.pk_id)select * from sub_nodesupdate IBP_T_Regions set iDeptid159 from sub_nodes pt where IBP_T_Regions.PK_IDpt.PK_ID