update hierarchy

[danko.greiner]

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]

da***********@gmail.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.ParentFolderId = p.FolderId
where c.FullPath <p.FullPath + '/' + c.FolderName

if @@rowcount = 0
break

end

[--CELKO--]

>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]

Look up "materialised 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]