Home Forums SQL Server 2016 SQL Server 2016 - Development and T-SQL Using CTE to update multiple columns in table and loop on all rows in table RE: Using CTE to update multiple columns in table and loop on all rows in table

  • August 16, 2017 at 9:51 am

    #1955525

    Chris - thanks for the reply!  I tried to run your code, modified to include additional hierarchy elements, but it doesn't store the other values. Another thing is I'm traversing from bottom to top - for the default case you have 'WHERE ParentID IS NULL' whereas I have 'WHERE ID = @TID' which starts at the bottom. 

    I also realized that I am assigning the wrong values -- 'Loc_*' must be set to the values of 'Name' for each level in the hierarchy.  How can I obtain all values of Name?

    Here is the new code:

    DROP TABLE dbo.SimpleH;
    CREATE TABLE dbo.SimpleH (ID INT NOT NULL PRIMARY KEY CLUSTERED, ParentID INT NULL, [Name] VARCHAR(30) NOT NULL, [Level] TINYINT NULL, [Shelf] VARCHAR(30) NULL, [Rack] VARCHAR(30) NULL, [Box] VARCHAR(30) NULL);
    INSERT INTO SimpleH (ID, ParentID, [Name], [Shelf], [Rack], [Box]) VALUES (1, NULL, 'Shelf1','','','')
    INSERT INTO SimpleH (ID, ParentID, [Name], [Shelf], [Rack], [Box]) VALUES (2, 1, 'Rack1','','','') 
    INSERT INTO SimpleH (ID, ParentID, [Name], [Shelf], [Rack], [Box]) VALUES (3, 1, 'Rack2','','','')  
    INSERT INTO SimpleH (ID, ParentID, [Name], [Shelf], [Rack], [Box]) VALUES (4, 1, 'Rack3','','','')  
    INSERT INTO SimpleH (ID, ParentID, [Name], [Shelf], [Rack], [Box]) VALUES (5, 2, 'Box1','','','')
    INSERT INTO SimpleH (ID, ParentID, [Name], [Shelf], [Rack], [Box]) VALUES (6, 2, 'Box2','','','')
    INSERT INTO SimpleH (ID, ParentID, [Name], [Shelf], [Rack], [Box]) VALUES (7, 3, 'Box1','','','')
    INSERT INTO SimpleH (ID, ParentID, [Name], [Shelf], [Rack], [Box]) VALUES (8, 3, 'Box2','','','')
    INSERT INTO SimpleH (ID, ParentID, [Name], [Shelf], [Rack], [Box]) VALUES (9, 4, 'Box1','','','')
    INSERT INTO SimpleH (ID, ParentID, [Name], [Shelf], [Rack], [Box]) VALUES (10, 4, 'Box2','','','')
    INSERT INTO SimpleH (ID, ParentID, [Name], [Shelf], [Rack], [Box]) VALUES (11, NULL, 'Shelf2','','','')
    INSERT INTO SimpleH (ID, ParentID, [Name], [Shelf], [Rack], [Box]) VALUES (12, 11, 'Rack1','','','') 
    INSERT INTO SimpleH (ID, ParentID, [Name], [Shelf], [Rack], [Box]) VALUES (13, 11, 'Rack2','','','')  
    INSERT INTO SimpleH (ID, ParentID, [Name], [Shelf], [Rack], [Box]) VALUES (14, 11, 'Rack3','','','')
    INSERT INTO SimpleH (ID, ParentID, [Name], [Shelf], [Rack], [Box]) VALUES (15, 12, 'Box1','','','')
    INSERT INTO SimpleH (ID, ParentID, [Name], [Shelf], [Rack], [Box]) VALUES (16, 12, 'Box2','','','') 
    INSERT INTO SimpleH (ID, ParentID, [Name], [Shelf], [Rack], [Box]) VALUES (17, 13, 'Box1','','','')
    INSERT INTO SimpleH (ID, ParentID, [Name], [Shelf], [Rack], [Box]) VALUES (18, 13, 'Box2','','','')
    INSERT INTO SimpleH (ID, ParentID, [Name], [Shelf], [Rack], [Box]) VALUES (19, 14, 'Box1','','','')
    INSERT INTO SimpleH (ID, ParentID, [Name], [Shelf], [Rack], [Box]) VALUES (20, 14, 'Box2','','','')

    ;WITH rCTE AS (
     SELECT [NewLevel] = 1, ID, ParentID, [Name],shelf,rack,box
     FROM dbo.SimpleH
     WHERE ParentID IS NULL
     UNION ALL
     SELECT [NewLevel] = ls.[NewLevel]+1, cs.ID, cs.ParentID, cs.[Name], cs.shelf,cs.rack,cs.box
     FROM rCTE ls
     INNER JOIN dbo.SimpleH cs ON cs.ParentID = ls.ID
    )
    UPDATE s SET [Level] = r.NewLevel, shelf = r.shelf, rack = r.rack, box = r.box,
    [Name] = r.[Name]
    FROM dbo.SimpleH s
    INNER JOIN rCTE r ON r.ID = s.ID;

    If I do a SELECT rather than an UPDATE, the new r.name is identical to the original name:
    SELECT s.*, r.NewLevel,r.[Name]
    FROM dbo.SimpleH s
    INNER JOIN rCTE r ON r.ID = s.ID;