Recursive Function
-
hi,
i want some recursive function or stored procedure for the following purpose.
i have a table containing 3 cols id , parentid , val
the values in table are as under
1 0 abc
2 0 cde
3 1 NULL
4 3 NULL
now i want something that will update the value of val col of root that is whose parent is 0 to all of its followers...
-
Take your post and expand it some. You provide some sample data, show us what the final results of the query you are try to write would be. Your description leaves me a little short as I am much more of a visually oriented individual when it comes to solving some problems, like this one.
Also, for tips on how to ask for help that will get you better responses, read the article listed below.
-
[font="Verdana"]
vasaharshit (11/4/2008)
hi,i want some recursive function or stored procedure for the following purpose.
i have a table containing 3 cols id , parentid , val
the values in table are as under
1 0 abc
2 0 cde
3 1 NULL
4 3 NULL
now i want something that will update the value of val col of root that is whose parent is 0 to all of its followers...
From your post what I have understood is that you wants to update the parent value to its child...
If so, then why you are going for recursive function/proc if it can be achieved through single update statement? First let us know with your sameple data and the expected O/P.
Mahesh[/font]
MH-09-AM-8694
-
vasaharshit (11/4/2008)
hi,i want some recursive function or stored procedure for the following purpose.
i have a table containing 3 cols id , parentid , val
the values in table are as under
1 0 abc
2 0 cde
3 1 NULL
4 3 NULL
now i want something that will update the value of val col of root that is whose parent is 0 to all of its followers...
You don't need recursion.
update T set T.value=T1.value
from table1 T join table1 T1 on T1.id=T.parent_id
Execute this update depth times and all children of 1 will have 'abc', children of 2 'cde', etc. In ssms you can add Go nn to execute it nn times:
update T set T.value=T1.value
from table1 T join table1 T1 on T1.id=T.parent_id
go 10
-
Maybe this?
WITH CTE AS (SELECT ID,Val AS RootVal
FROM MyTable
WHERE PARENTID=0
UNION ALL
SELECT t.ID,c.RootVal
FROM CTE c
INNER JOIN MyTable t ON t.PARENTID=c.ID)
UPDATE t
SET Val=c.RootVal
FROM MyTable t
INNER JOIN CTE c ON c.ID=t.ID
WHERE t.PARENTID<>0
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply