how to change a parent id of child and subchilds in hierarchyid

Question

id       hierid
---------------------
2   |    /2/
7   |    /2/7/
8   |    /2/8/
11  |    /2/7/11/
13  |    /2/8/13/
17  |    /2/8/17/
37  |    /2/8/37/

i want to change the root of this to /3/ that means the output i want to get is

id     hierid
---------------------
2   | /3/
7   | /3/7/
8   | /3/8/
11  | /3/7/11/
13  | /3/8/13/
17  | /3/8/17/
37  | /3/8/37/

like this how can i do it using hierarchyid

score 4 · Accepted Answer · answered May 04 '11 at 14:37

4

UPDATE  mytable
SET     hierid = hierid.GetReparentedValue('/2/', '/3/')
WHERE   hierid.IsDescendantOf('/2/') = 1

answered May 04 '11 at 14:37

Quassnoi

413,100
91
616
614

score 2 · Answer 2 · answered May 04 '11 at 14:36

2

How about this:

UPDATE tbl SET hierid = REPLACE(hierid, '/2/', '/3/');

answered May 04 '11 at 14:36

Nik

7,113
13
51
80

2

UPDATE tbl SET hierid = REPLACE(hierid.ToString(), '/2/', '/3/') – Nighil May 04 '11 at 14:47
because it is hierarchyid so want to convert to string using ToString() – Nighil May 04 '11 at 14:59

how to change a parent id of child and subchilds in hierarchyid

2 Answers2