update hierarchy

[danko.greiner@gmail.com]

I have structure:

FolderId, FolderName, ParentFolderId, FullPath

e.g.

1, First, null, First
2, Sec, 1, First/Sec
.....

When update happens to FolderName, i need to update all FullPaths that
are below Folder which is changing.

Any ideas?
Thank you.

[Ed Murphy]

Re: update hierarchy

danko.greiner@g mail.com wrote:

I have structure:
>
FolderId, FolderName, ParentFolderId, FullPath
>
e.g.
>
1, First, null, First
2, Sec, 1, First/Sec
.....
>
When update happens to FolderName, i need to update all FullPaths that
are below Folder which is changing.

Untested:

declare @FolderId int
set @FolderId = 2

declare @FolderName varchar(10)
set @FolderName = 'Zwei'

update the_table
set FolderName = @FolderName
where FolderId = @FolderId

while 1 = 1
begin

update the_table
set c.FullPath = p.FullPath + '/' + c.FolderName
from the_table c
join the_table p on c.ParentFolderI d = p.FolderId
where c.FullPath <p.FullPath + '/' + c.FolderName

if @@rowcount = 0
break

end

[--CELKO--]

Re: update hierarchy

>Any ideas? <<

Get a copy of TREES & HIERARCHIES IN SQL and look up the Nested Sets
model. This problem is trivial with the right data model.

[Tony Rogerson]

Re: update hierarchy

Look up "materialis ed path" and use a "surrogate key" that way when the
folder name changes the path remains the same because the surrogate key is
immutable (doesn't change).

Tony.

--
Tony Rogerson, SQL Server MVP

http://sqlblogcasts.com/blogs/tonyrogerson

[Ramblings from the field from a SQL consultant]

http://sqlserverfaq.com

[UK SQL User Community]