update hierarchy

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.

Re: update hierarchy

danko.greiner@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

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.

Re: update hierarchy

(danko.greiner@gmail.com) writes:
> 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.

Obviously the best would be a trigger. Here is a solution for SQL 2005:

CREATE TABLE paths(folderid int NOT NULL PRIMARY KEY,
name nvarchar(50) NOT NULL,
parentid int NULL REFERENCES paths(folderid),
fullpath nvarchar(4000) NULL)
go
CREATE TRIGGER path_tri ON paths AFTER INSERT, UPDATE AS
WITH recurs_up AS (
SELECT folderid, parentid, convert(nvarchar(4000), name) AS fullpath
FROM inserted
UNION ALL
SELECT r.folderid, p.parentid,
convert(nvarchar(4000), p.name + '/' + r.fullpath)
FROM paths p
JOIN recurs_up r ON p.folderid = r.parentid
)
UPDATE paths
SET fullpath = r.fullpath
FROM paths p
JOIN recurs_up r ON p.folderid = r.folderid
WHERE r.parentid IS NULL;

WITH recurs_down AS (
SELECT p.folderid, p.fullpath
FROM paths p
WHERE EXISTS (SELECT *
FROM inserted i
WHERE i.folderid = p.folderid)
UNION ALL
SELECT p.folderid, r.fullpath + '/' + p.name
FROM paths p
JOIN recurs_down r ON r.folderid = p.parentid
)
UPDATE paths
SET fullpath = r.fullpath
FROM paths p
JOIN recurs_down r ON p.folderid = r.folderid
go
INSERT paths (folderid, name, parentid)
VALUES (1, 'Top', NULL)
INSERT paths (folderid, name, parentid)
VALUES (2, 'Left', 1)
INSERT paths (folderid, name, parentid)
VALUES (3, 'Right', 1)
INSERT paths (folderid, name, parentid)
VALUES (4, 'A', 2)
INSERT paths (folderid, name, parentid)
VALUES (5, 'B', 2)
INSERT paths (folderid, name, parentid)
VALUES (6, 'C', 3)
INSERT paths (folderid, name, parentid)
VALUES (7, 'D', 3)
go
SELECT * FROM paths ORDER BY fullpath
go
UPDATE paths
SET name = 'Gauche'
WHERE folderid = 2
go
SELECT * FROM paths ORDER BY fullpath
go
DROP TABLE paths



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: update hierarchy

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]