locked
Recursive update in SQL Server 2008 RRS feed

  • Question

  • User841325827 posted

    I'm somewhat new to SQL and I am trying to figure out the best way of doing this without hard coding update statements in SQL Server 2008. I have an issue with a query against one of our MS SQL Server databases. I have a hierarchical table of companies with columns :

    KID (bigint) , ParentID (bigint),PS(decimal), SGS(decimal) , OR(int)
    KID   ParentID    PS     SGS     OR
    -------------------------------------
    1       0        5000     0      21
    2       1        120      0      10
    3       1        800      0       8
    4       2        200      0      18
    5       2        500      0      3

    I must write a recursive query with this statement : 

    if a node have child , and (child's OR<21) then SGS of node = ( child's PS + child's SGS ).


     I must run this for any node , recursively and write this statement in SQL :

    create PROCEDURE [dbo].[UpdateSGS]
    (
        @KID bigint,
        @SGS decimal
    )
    as
    begin
        set nocount on
    
        update Chart
        set SGS = @SGS
        where KID = @KID
    
        ;with Hierarchy(KID, ParentID, SGS, NewSGS)
        as (
            select D.KID, D.ParentID, D.SGS , D.PS + D.SGS
            from Chart D
            where KID = @KID
            union all
            select D.KID, D.ParentID,
                H.NewSGS , H.NewSGS + D.PS
            from Hierarchy H
                join Chart D on D.ParentID = H.KID
        )
        update D
        set D.SGS = H.NewSGS
        from Chart D
            join Hierarchy H on H.KID = D.KID
        where
            D.KID != @KID
    end

    but it's not complete and I get an error:

    Msg 240, Level 16, State 1, Procedure UpdateSGS, Line 14 Types don't match between the anchor and the recursive part in column "SGS" of recursive query "Hierarchy".

    Monday, November 30, 2015 8:00 AM

Answers

  • User-808054615 posted

    Try:

    ;with 
        CTE_Hierarchy as 
        (
            select KID, ParentID, SGS , PS, [OR], CAST(KID as varchar(max)) as HID
            from Chart 
            where KID = 1 -- @KID
    
            union all
    
            select D.KID, D.ParentID, D.SGS, D.PS, D.[OR], H.HID + '.' + CAST(D.KID as varchar(max))
            from CTE_Hierarchy H
            inner join Chart D 
                on D.ParentID = H.KID
        ),
        
        CTE_Sum as
        (
            select 
                H.KID, 
                (select SUM(S.SGS) + SUM(S.PS) 
                 from CTE_Hierarchy S
                 where 
                     S.[OR] < 21 AND
                     S.HID like H.HID + '.%') as NewSGS
            from CTE_Hierarchy H
            group by
                H.KID,
                H.HID
        )
    
    update D
    set D.SGS = COALESCE(S.NewSGS, 0)
    from Chart D
    inner join CTE_Sum S
        on S.KID = d.KID
    

    Hope this helps.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 30, 2015 8:00 PM
  • User-808054615 posted

    So I believe that this parameter is not necessary.

    Try changing the condition below:

            where KID = @KID
    

    by:

            where ParentID = 0
    

    Hope this helps.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 1, 2015 4:15 PM

All replies

  • User-808054615 posted

    Try:

    ;with Hierarchy(KID, ParentID, SGS, NewSGS)
        as (
            select D.KID, D.ParentID, D.SGS , cast(D.PS + D.SGS as decimal)
            from Chart D
            where KID = @KID
            union all
            select D.KID, D.ParentID,
                cast(H.NewSGS as decimal), cast(H.NewSGS + D.PS as decimal)
            from Hierarchy H
                join Chart D on D.ParentID = H.KID
        )
        update D
        set D.SGS = H.NewSGS
        from Chart D
            join Hierarchy H on H.KID = D.KID
        where
            D.KID != @KID

    Hope this helps.

    Monday, November 30, 2015 11:30 AM
  • User841325827 posted

    Thanks , Error fixed.

    But I think my query is not true , because this query must get KID from user and calculate SGS for any node recursively. 

    Monday, November 30, 2015 12:28 PM
  • User-808054615 posted

    Have you tested with which parameter values?

    How did your table after the command and how you should be?

    Monday, November 30, 2015 12:51 PM
  • User841325827 posted

    recursive update

    yes , i test my query. here , i have two parameters : @KID and @SGS

    create PROCEDURE [dbo].[UpdateSGS]
    (
        @KID bigint,
        @SGS decimal
    )

    I wrote this query from this link  SQL Server : recursive update statement , but i have just one parameter (KID = Node's id ).

    In each node if that node have child , ( and Child's or <21 ) SGS of node = (child's ps + child's SGS) .

    for example : E have child (F) and OR of F is 0 (<21) then = SGS(E) = SGS(F) + PS(F).

    Could  you help me , please ? 

    Monday, November 30, 2015 2:39 PM
  • User-808054615 posted

    From what I understand, in your example the result of SGS(E) would be 0 ( SGS(E) = SGS(F) + PS(F) = 0 + 0 = 0). 

    Would it be this? If so, what would be the purpose of @SGS parameter?

    Monday, November 30, 2015 4:28 PM
  • User841325827 posted

    For example :
    A has two child {B , C } , OR of both of theme is < 21 then SGS(A) = (SGS (B) + PS (B)) + (SGS(C)+PS(c))
    that now we must calculate SGS(B) and SGS(C) , ....

    SGS(A) = (SGS (B) + PS (B)) + (SGS(C)+PS(c)) = ( 0 + 100 ) + ( 500 + 100 ) = 700
    SGS(B) = 0 beacuse B don't have any child.
    SGS(C) = (SGS(D) + PS(D)) + (SGS(E)+PS(E)) = (0 + 0) + (0 + 500) = 500
    SGS(D) = 0 beacuse D don't have any child.
    SGS(E) = SGS(F) + PS(F) = 0 + 0 = 0
    SGS(F) = 0 beacuse F don't have any child.

    Monday, November 30, 2015 5:54 PM
  • User-808054615 posted

    Try:

    ;with 
        CTE_Hierarchy as 
        (
            select KID, ParentID, SGS , PS, [OR], CAST(KID as varchar(max)) as HID
            from Chart 
            where KID = 1 -- @KID
    
            union all
    
            select D.KID, D.ParentID, D.SGS, D.PS, D.[OR], H.HID + '.' + CAST(D.KID as varchar(max))
            from CTE_Hierarchy H
            inner join Chart D 
                on D.ParentID = H.KID
        ),
        
        CTE_Sum as
        (
            select 
                H.KID, 
                (select SUM(S.SGS) + SUM(S.PS) 
                 from CTE_Hierarchy S
                 where 
                     S.[OR] < 21 AND
                     S.HID like H.HID + '.%') as NewSGS
            from CTE_Hierarchy H
            group by
                H.KID,
                H.HID
        )
    
    update D
    set D.SGS = COALESCE(S.NewSGS, 0)
    from Chart D
    inner join CTE_Sum S
        on S.KID = d.KID
    

    Hope this helps.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 30, 2015 8:00 PM
  • User841325827 posted

    Thank you very much . A great help for me. :)

    Tuesday, December 1, 2015 5:25 AM
  • User841325827 posted

    I Initialized @KID in asp.net . This can be done through sql?

    Tuesday, December 1, 2015 5:51 AM
  • User-808054615 posted

    Sorry but I did not understand your question.

    Tuesday, December 1, 2015 11:22 AM
  • User841325827 posted

    here at this part , we must initialize KID :

    ;with 
        CTE_Hierarchy as 
        (
            select KID, ParentID, SGS , PS, [OR], CAST(KID as varchar(max)) as HID
            from Chart 
            where KID = @KID
    
            union all
    
            select D.KID, D.ParentID, D.SGS, D.PS, D.[OR], H.HID + '.' + CAST(D.KID as varchar(max))
            from CTE_Hierarchy H
            inner join Chart D 
                on D.ParentID = H.KID
        ),

    in the other words , can set @KID in sql ?

    Tuesday, December 1, 2015 1:17 PM
  • User-808054615 posted

    The parameter would initialized with what value?

    Tuesday, December 1, 2015 1:39 PM
  • User841325827 posted

    I write this code in c# :

    DataTable dt = dal.ExecuteDataTable(CommandType.Text, "SELECT KID FROM Chart", new SqlParameter[] { });
    
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        KFormula.RecurciveUpdateSGS(Convert.ToInt64(dt.Rows[i]["KID"])); // in this function i used recursive query
    }

    i must get all KID from table , and then execute query that you write for each KID.

    My question : can i get all KID and then execute Query for each row in the same query ?

    Tuesday, December 1, 2015 3:25 PM
  • User-808054615 posted

    So I believe that this parameter is not necessary.

    Try changing the condition below:

            where KID = @KID
    

    by:

            where ParentID = 0
    

    Hope this helps.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 1, 2015 4:15 PM
  • User841325827 posted

    Yes , you are right . thank you so much :)

    Tuesday, December 1, 2015 5:31 PM