Hierarchy update all children - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Hierarchy update all children

Author

Topic

TheMaxx
Starting Member

5 Posts

Posted - 2007-11-06 : 09:57:32

I have structure:
FolderId, FolderName, ParentFolderId, FullPath

Data:
1, First, null, First
2, Second, 1, First/Sectond
3, Third, 2, First/Second/Third
and so on...

I need to do:
When change happens to column 'Name' i need to update FullPath, and FullPath of ALL children (and children's children and so on...)

Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-06 : 10:38:09

[code]DECLARE @Sample TABLE (ID INT, Nam SYSNAME, Parent INT, FullPath SYSNAME)

INSERT @Sample
SELECT 1, 'First', null, 'First' UNION ALL
SELECT 2, 'Second', 1, 'First/Second' UNION ALL
SELECT 3, 'Third', 2, 'First/Second/Third' UNION ALL
SELECT 4, 'Fourth', 1, 'First/Fourth'

UPDATE @Sample
SET Nam = 'Peso'
WHERE Nam = 'Second'

UPDATE @Sample
SET FullPath = REPLACE('/' + FullPath + '/', '/Second/', '/Peso/')
WHERE '/' + FullPath + '/' LIKE '%/Second/%'

UPDATE @Sample
SET FullPath = SUBSTRING(FullPath, 2, LEN(FullPath) - 2)
WHERE FullPath LIKE '/%/'

SELECT * FROM @Sample[/code]

E 12°55'05.25"
N 56°04'39.16"

TheMaxx
Starting Member

5 Posts

Posted - 2007-11-06 : 12:54:24

This would work only if FolderName is UNIQUE (which unforunatly is not the case)

Your code would also update all Folders which have 'Second' in name, like "TwentySecond" and so on... and that is not accaptable.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-06 : 13:58:40

Are you for real?
Why do you think I have put both leading and trrailing delimeters to the column...

Please try to code and then come back to report your progress.

E 12°55'05.25"
N 56°04'39.16"

TheMaxx
Starting Member

5 Posts

Posted - 2007-11-07 : 04:30:25

Let's say you have:

1, First, null, First
2, Second, 1, First/Second
3, Third, 2, First/Second/Third
4, Second, 3, First/Second/Third/Second

So, FolderName is not UNIQUE, you can have same FolderName in different nodes of hiearchy, representing different folders (they differ on FolderId, ParentFolderId and FullPath)

Your code would update ALL folders which have name 'Second' and i need just updating specific one.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-07 : 04:50:02

Now we finally got the full picture and an explanation of your business rules!

Use same technique as above.
I hope you are satisfied with this solution.

-- Prepare sample data to mimic your environment
DECLARE	@Sample TABLE (ID INT, Nam SYSNAME, Parent INT, FullPath SYSNAME)

INSERT	@Sample
SELECT	1, 'First', NULL, 'First' UNION ALL
SELECT	2, 'Second', 1, 'First/Second' UNION ALL
SELECT	3, 'Third', 2, 'First/Second/Third' UNION ALL
SELECT	4, 'Second', 3, 'First/Second/Third/Second'

-- Show original data
SELECT * FROM @Sample

DECLARE	@NewName VARCHAR(20),	-- Should be a parameter in a stored procedure
	@EditID INT,		-- Should be a parameter in a stored procedure
	@PreviousChar INT,
	@NextChar INT,
	@Path SYSNAME

SELECT	@NewName = 'Peso',	-- Simulating a parameter in a stored procedure
	@EditID = 2		-- Simulating a parameter in a stored procedure

-- Update Nam
UPDATE	@Sample
SET	Nam = @NewName
WHERE	ID = @EditID

-- Get current positions in fullpath
SELECT	@NextChar = LEN(FullPath) + 1,
	@PreviousChar = @NextChar - CHARINDEX('/', REVERSE(FullPath)), 
	@Path = FullPath + '%'
FROM	@Sample
WHERE	ID = @EditID

-- Update the fullpath accordingly
UPDATE	@Sample
SET	FullPath = LEFT(FullPath, @PreviousChar) + @NewName + SUBSTRING(FullPath, @NextChar, 100)
WHERE	FullPath LIKE @Path

-- Show the new data
SELECT	*
FROM	@Sample

Change the @EditID = 2 to @EditID = 4 and you will see that this will work.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-07 : 04:52:01

Your stored procedure could look something similar to this

CREATE PROCEDURE dbo.uspUpdateNodeName
(
	@FolderID INT,
	@NewFolderName VARCHAR(200)
)
AS

SET NOCOUNT ON

DECLARE	@PreviousChar INT,
	@NextChar INT,
	@FullPath VARCHAR(8000)

-- Update Nam
UPDATE	{YourTableNameHere}
SET	FolderName = @NewFolderName
WHERE	FolderID = @FolderID

-- Get current positions in fullpath
SELECT	@NextChar = LEN(FullPath) + 1,
	@PreviousChar = @NextChar - CHARINDEX('/', REVERSE(FullPath)), 
	@FullPath = FullPath + '%'
FROM	{YourTableNameHere}
WHERE	FolderID = @FolderID

-- Update the fullpath accordingly
UPDATE	{YourTableNameHere}
SET	FullPath = LEFT(FullPath, @PreviousChar) + @NewFolderName + SUBSTRING(FullPath, @NextChar, 8000)
WHERE	FullPath LIKE @FullPath

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources