SQL uses recursion to update tree-Level Table records

 

The table structure is as follows:

Org_info

Org_id, org_parent_id, org_parent_path

The org_parent_path field must be updated. The field value is org_parent_path + '.' of the parent node + org_id of the parent node.

 

Ideas

First find the root node and set its org_parent_path

Recursively locate the child node and update the org_parent_path field value.

 

Code As follows:

Code

Declare   @ ID   Nvarchar ( 36 )
Set   @ ID   =   ' 549c2e32-3927-4e5e-aa26-993b460d067a ' ; -- -Modify the parent node here

With Rootnodecteee (org_id, org_parent_id, org_parent_path)
As
(
Select Org_id, org_parent_id, org_parent_path From Org_info Where Org_id In ( @ ID )
Union   All
Select Org_info.org_id, org_info.org_parent_id, org_info.org_parent_path From Rootnodecteee
Inner   Join Org_info
On Rootnodecteee. org_id = Org_info.org_parent_id
)
Select   *   Into # T From Rootnodecteee
-- Select * from # T
Declare   @ Org_id   Varchar ( 36 ) --
Declare   @ Org_parent_id   Varchar ( 36 ) --
Declare   @ Org_parent_path   Varchar ( 512 ) --
Declare   @ Temporg_parent_path   Varchar ( 512 ) --
Declare Row Cursor   For -- Declared cursor row
Select Org_id, org_parent_id, org_parent_path From # T --
-- Select * From rootnodecteee

Open Row
Fetch   Next   From Row Into   @ Org_id , @ Org_parent_id , @ Org_parent_path --
Fetch   Next   From Row Into   @ Org_id , @ Org_parent_id , @ Org_parent_path --
While   @ Fetch_status   =   0 -- Completion status
Begin
Print ( @ Org_id + ' __ ' + @ Org_parent_id + ' __ ' + @ Org_parent_path )
Select   @ Temporg_parent_path = Org_parent_path +   ' . ' + Org_id From Org_info Where Org_id = @ Org_parent_id
Update Org_info Set Org_parent_path =   @ Temporg_parent_path   Where Org_id =   @ Org_id  

Fetch Next FromRowInto @ Org_id,@ Org_parent_id,@ Org_parent_path 

End
CloseRow
DeallocateRow

Drop Table# T

 

 

Summary

Use with to Recursively search for data to generate tables

Of course, you can also write SP directly.

Use livewriter for the first time

I don't know how to set the code format and abstract.

 

Published as a diary

Webpage entry blog

Manual change

Manual convenience