none
Updating a chain of records by a CTE - SQL Server 2005

    Question

  • Hi,

    I've the following SQL Server 2005 table:

    CREATE TABLE [dbo].[WOr](
    	[WO] [varchar](10) NULL,
    	[WO_Parent] [varchar](10) NULL,
    	[WO_Group] [varchar](10) NULL
    ) ON [PRIMARY]

    with these test values:

    INSERT INTO [WOr] ([WO] ,[WO_Parent] ,[WO_Group])
    VALUES ('2000', NULL, NULL)
    INSERT INTO [WOr] ([WO] ,[WO_Parent] ,[WO_Group])
    VALUES ('2001', '2000', NULL)
    INSERT INTO [WOr] ([WO] ,[WO_Parent] ,[WO_Group])
    VALUES ('2002', '2001', NULL)
    INSERT INTO [WOr] ([WO] ,[WO_Parent] ,[WO_Group])
    VALUES ('2003', '2002', NULL)
    INSERT INTO [WOr] ([WO] ,[WO_Parent] ,[WO_Group])
    VALUES ('5000', NULL, NULL)
    INSERT INTO [WOr] ([WO] ,[WO_Parent] ,[WO_Group])
    VALUES ('5001', '5000', NULL)
    INSERT INTO [WOr] ([WO] ,[WO_Parent] ,[WO_Group])
    VALUES ('5002', '5001', NULL)
    INSERT INTO [WOr] ([WO] ,[WO_Parent] ,[WO_Group])
    VALUES ('5003', '5002', NULL)

    I need to updating the WO_group column with the WO value of the first item of the chain of records.

    In this sample, a chain begins with the record having WO = 2000. This recors hasn't any parent record. The record having WO = 2001 has the record with WO = 2000 as a parent and so on.

    The goal is to update the chain from WO = 2000 to WO = 2003 setting the WO_group = 2000.

    I'm trying to use a CTE:

    WITH CTE(WO, WO_parent, wo_group, Level) AS
    (
    SELECT WO, WO_parent, wo_group, 0 as Level
    FROM WOr WHERE WO_Parent IS NULL
    UNION ALL
    SELECT W1.WO, W1.WO_parent, w1.wo_group, Level + 1
    FROM WOr as W1 INNER JOIN CTE as W2
    on W1.WO_parent = W2.WO
    )

    but I can setting the WO_group column only for the records without a parent.

    Any suggests to me, please? Thanks

    Tuesday, July 08, 2014 10:23 PM

Answers

  • Hi!

    I believe you can easily use your CTE with only minor modifications to achieve your goal:

    ;WITH CTE(WO, WO_parent, wo_group, Level) AS
    (
    SELECT WO, WO AS WO_parent, wo_group, 0 as Level
    FROM WOr WHERE WO_Parent IS NULL
    UNION ALL
    SELECT W1.WO, W2.WO_parent, w1.wo_group, Level + 1
    FROM WOr as W1 INNER JOIN CTE as W2
    on W1.WO_parent = W2.WO
    )
    UPDATE WOr
    SET wo_group = CTE.WO_Parent
    FROM WOr
    	INNER JOIN CTE
    	ON CTE.WO = WOr.WO

    Alexander Karavaev, PMP, MCITP, MCTS
    itconsult.azurewebsites.net

    Wednesday, July 09, 2014 12:11 AM

All replies

  • Hi!

    I believe you can easily use your CTE with only minor modifications to achieve your goal:

    ;WITH CTE(WO, WO_parent, wo_group, Level) AS
    (
    SELECT WO, WO AS WO_parent, wo_group, 0 as Level
    FROM WOr WHERE WO_Parent IS NULL
    UNION ALL
    SELECT W1.WO, W2.WO_parent, w1.wo_group, Level + 1
    FROM WOr as W1 INNER JOIN CTE as W2
    on W1.WO_parent = W2.WO
    )
    UPDATE WOr
    SET wo_group = CTE.WO_Parent
    FROM WOr
    	INNER JOIN CTE
    	ON CTE.WO = WOr.WO

    Alexander Karavaev, PMP, MCITP, MCTS
    itconsult.azurewebsites.net

    Wednesday, July 09, 2014 12:11 AM
  • Hi Alexander, thanks for your reply.

    You have adjusted W1.WO_parent with W2.WO_parent inside the CTE definition. The update statement functions correctly.

    I'd like try it when I have two childs for the same parent or two parents for the same child.

    Thanks

    Wednesday, July 09, 2014 8:02 AM
  • The UPDATE statement functions rightly also for two childs for the same parent.

    As soon as possible I will try in presence of two parents for the same child.

    Thanks

    Wednesday, July 09, 2014 8:05 AM