Using CTE to update multiple columns in table and loop on all rows in table

Question

I'm a SQL newbie and am trying to use a CTE on all rows in a table, to update multiple columns in each row with hierarchical information. I've read lots of examples online but none that describes exactly what I need, so I hope someone can help me.

I have a table of study samples stored in a hierarchy of shelf->rack->box, where shelf is the highest level and box is the lowest, with the samples below box. Shelf, rack, and box are also in this table as separate rows. All items have a field called 'name' to identify them. They are all connected by ID and parent_ID. Kind of like a management structure.

‌‌‌I need to populate all of the level values in each row for samples. It's more complicated than the manager-employee examples I've found online, and it requires storing the values returned by the CTE rather than simply doing a SELECT to display them, as the examples I've found show.

To run the CTE on all rows, ‌‌I've tried nesting the whole CTE in a WHILE loop but it just loops on a single value rather than all rows.

Below is the code I currently have working to return the hierarchy of one sample value; 'tempspec' is the sample table:

DECLARE @TID float;
SET @TID = 39059;
WITH cte AS
(
   SELECT ID,
    Parent_Id,
    Name,
    Study_ID,
          Loc_Box,
          Loc_Shelf,
          Loc_Rack,
    Loc_Type
     FROM tempspec
    WHERE ID = @TID and Study_ID = 'XXX'
    UNION all
   SELECT tempspec.Id,
          tempspec.Parent_Id,
          tempspec.Name,
    tempspec.Study_ID,
          tempspec.Loc_Box,
          tempspec.Loc_Shelf,
          tempspec.Loc_Rack,
    tempspec.Loc_Type
     FROM tempspec
     JOIN cte on tempspec.Id = cte.Parent_Id
)
SELECT E1.name, E1.ID, E1.Parent_ID,E1.Loc_Type,ISNULL(E2.name,'TOP')
FROM cte E1
LEFT JOIN cte E2
ON E1.parent_id = E2.ID;

But if I try to replace the SELECT with an UPDATE, it runs and says all rows in the table have been updated, but nothing has been stored:

UPDATE E1
SET E1.Loc_Box = E2.Loc_Box,
 E1.Loc_Rack = E2.Loc_Rack,
 E1.Loc_Shelf = E2.Loc_Shelf
FROM tempspec E1
LEFT JOIN cte E2
ON E1.parent_id = E2.ID;

‌ And if I try to nest the CTE in this WHILE loop ('P' indicates sample rows, so I am only traversing the tree for them), I can't assign the ID to @TID, or when I've tried it other ways it loops forever:

WHILE EXISTS(SELECT * FROM tempspec WHERE Loc_Type = 'P')
BEGIN
SET @TID = ID;

What am I doing wrong? I've tried various formats but nothing seems to work. Thanks in advance for your help!‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌

Below is the requested test data. I want to populate Shelf, Rack, and Box with the Name values from the parent rows for the samples (type='P'):

DROP TABLE dbo.TEST_DATA;
CREATE TABLE dbo.TEST_DATA (int ID, int Parent_Id,varchar(30) Name, varchar(10) Type,varchar(30) Shelf,varchar(30) Rack,varchar(30) Box)
INSERT INTO TEST_DATA (39702,   1664,   0228MBDNAERA1,  'P','','','');
INSERT INTO TEST_DATA (39703,   1664,   0230MBDNAERA1,  'P','','','');
INSERT INTO TEST_DATA (39704,   1664,   0231MBDNAERA1,  'P','','','');
INSERT INTO TEST_DATA (39726,   1744,   0228MBDNAERA2,  'P','','','');
INSERT INTO TEST_DATA (39727,   1744,   0230MBDNAERA2,  'P','','','');
INSERT INTO TEST_DATA (39728,   1744,   0231MBDNAERA2,  'P','','','');
INSERT INTO TEST_DATA (39764,   1752,   0228MBDNAERA3,  'P','','','');
INSERT INTO TEST_DATA (39766,   1752,   0230MBDNAERA3,  'P','','','');
INSERT INTO TEST_DATA (39768,   1752,   0231MBDNAERA3,  'P','','','');
INSERT INTO TEST_DATA (1744,    1652,   MBDNAERA2 - 3,  'B','','','');
INSERT INTO TEST_DATA (1752,    1732,   MBDNAERA3 - 3,  'B','','','');
INSERT INTO TEST_DATA (1664,    1652,   MBDNAERA1 - 3,  'B','','','');
INSERT INTO TEST_DATA (1732,    1617,   Rack R, 'R','','','');
INSERT INTO TEST_DATA (1652,    1617,   Rack Q, 'R','','','');
INSERT INTO TEST_DATA (1617,    2,  Shelf 4,    'S','','','');
INSERT INTO TEST_DATA (2,   NULL,   Freezer,    'F','','','');

Example data hierarchy for one sample and the resulting row I'm trying to get (sorry about the bad formatting):

ID       Parent_Id  Name            Type    Shelf   Rack    Box
39702   1664    0228MBDNAERA1   'P'         
1664    1652    MBDNAERA1 - 3   'B'         
1652    1617    Rack Q          'R'         
1617    2   Shelf 4         'S'         
2           NULL    Freezer         'F'         

ID       Parent_Id  Name            Type    Shelf   Rack    Box
39702   1664    0228MBDNAERA1   'P' Shelf 4 Rack Q  MBDNAERA1 - 3

Can you add an example of what your table looks like with sample data, and then what results you are currently getting from the sample data, and what results you are trying to get instead? — Tab Alleman, Aug 16 '17 at 13:53
Do you want to update each row to copy values from its parent, or do you want to push the root (ancestor) row's values all the way down through the hierarchy? In the first case you don't need a recursive CTE at all, and in the second you need to add additional columns to the recursive CTE result to carry down the root values. — David Browne - Microsoft, Aug 16 '17 at 13:59
David - yes, I want to update each row containing a sample from its parent (I don't need to update the shelf/rack/box rows, they are only to establish the hierarchy). So for sample A, I want its shelf/rack/box values to match those in its hierarchy from the corresponding shelf/rack/box rows. Does that make sense? I thought listing all the columns in the UNION ALL - SELECT statement pulled all the necessary fields down. — Selene, Aug 16 '17 at 15:33
I just 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 and assign them to a single sample row? — Selene, Aug 16 '17 at 17:17
Put in the question a Sample source and the expected result, please. — Maurício Pontalti Neri, Aug 16 '17 at 17:24
Sample data added...I could not format it properly, it came from Excel. — Selene, Aug 16 '17 at 19:47

Maurício Pontalti Neri · Answer 1 · 2017-08-16T21:47:25.103

Try on it:

DECLARE @TEST_DATA as TABLE  (ID int , Parent_Id int ,Name varchar(30) , Type varchar(10)  )
INSERT @TEST_DATA VALUES (39702,   1664,   '0228MBDNAERA1',  'P');
INSERT @TEST_DATA VALUES (39703,   1664,   '0230MBDNAERA1',  'P');
INSERT @TEST_DATA VALUES (39704,   1664,   '0231MBDNAERA1',  'P');
INSERT @TEST_DATA VALUES (39726,   1744,   '0228MBDNAERA2',  'P');
INSERT @TEST_DATA VALUES (39727,   1744,   '0230MBDNAERA2',  'P');
INSERT @TEST_DATA VALUES (39728,   1744,   '0231MBDNAERA2',  'P');
INSERT @TEST_DATA VALUES (39764,   1752,   '0228MBDNAERA3',  'P');
INSERT @TEST_DATA VALUES (39766,   1752,   '0230MBDNAERA3',  'P');
INSERT @TEST_DATA VALUES (39768,   1752,   '0231MBDNAERA3',  'P');
INSERT @TEST_DATA VALUES (1744,    1652,   'MBDNAERA2 - 3',  'B');
INSERT @TEST_DATA VALUES (1752,    1732,   'MBDNAERA3 - 3',  'B');
INSERT @TEST_DATA VALUES (1664,    1652,   'MBDNAERA1 - 3',  'B');
INSERT @TEST_DATA VALUES (1732,    1617,   'Rack R', 'R');
INSERT @TEST_DATA VALUES (1652,    1617,   'Rack Q', 'R');
INSERT @TEST_DATA VALUES (1617,    2,  'Shelf 4',    'S');
INSERT @TEST_DATA VALUES (2,   NULL,   'Freezer',    'F');

-- SELECT 
;with 
cte_product as (
Select *  from @TEST_DATA where Type = 'P'),
cte_box as (
Select *  from @TEST_DATA where Type = 'B'),
cte_shelf as (
Select *  from @TEST_DATA where Type = 'S'),
cte_rack as (
Select *  from @TEST_DATA where Type = 'R')
Select * from
cte_product p 
left join cte_box b
on
p.Parent_Id = b.ID
inner join cte_rack r
on
b.Parent_Id = r.ID
inner join cte_shelf s
on
r.Parent_Id = s.ID

Result

ID          Parent_Id   Name                           Type       ID          Parent_Id   Name                           Type       ID          Parent_Id   Name                           Type       ID          Parent_Id   Name                           Type
----------- ----------- ------------------------------ ---------- ----------- ----------- ------------------------------ ---------- ----------- ----------- ------------------------------ ---------- ----------- ----------- ------------------------------ ----------
39726       1744        0228MBDNAERA2                  P          1744        1652        MBDNAERA2 - 3                  B          1652        1617        Rack Q                         R          1617        2           Shelf 4                        S
39727       1744        0230MBDNAERA2                  P          1744        1652        MBDNAERA2 - 3                  B          1652        1617        Rack Q                         R          1617        2           Shelf 4                        S
39728       1744        0231MBDNAERA2                  P          1744        1652        MBDNAERA2 - 3                  B          1652        1617        Rack Q                         R          1617        2           Shelf 4                        S
39764       1752        0228MBDNAERA3                  P          1752        1732        MBDNAERA3 - 3                  B          1732        1617        Rack R                         R          1617        2           Shelf 4                        S
39766       1752        0230MBDNAERA3                  P          1752        1732        MBDNAERA3 - 3                  B          1732        1617        Rack R                         R          1617        2           Shelf 4                        S
39768       1752        0231MBDNAERA3                  P          1752        1732        MBDNAERA3 - 3                  B          1732        1617        Rack R                         R          1617        2           Shelf 4                        S
39702       1664        0228MBDNAERA1                  P          1664        1652        MBDNAERA1 - 3                  B          1652        1617        Rack Q                         R          1617        2           Shelf 4                        S
39703       1664        0230MBDNAERA1                  P          1664        1652        MBDNAERA1 - 3                  B          1652        1617        Rack Q                         R          1617        2           Shelf 4                        S
39704       1664        0231MBDNAERA1                  P          1664        1652        MBDNAERA1 - 3                  B          1652        1617        Rack Q                         R          1617        2           Shelf 4                        S

--UPDATE TABLE 

;with 
cte_product as (
Select *  from @TEST_DATA where Type = 'P'),
cte_box as (
Select *  from @TEST_DATA where Type = 'B'),
cte_shelf as (
Select *  from @TEST_DATA where Type = 'S'),
cte_rack as (
Select *  from @TEST_DATA where Type = 'R')
UPDATE D 
SET
     Shelf = S.name
    ,Rack = r.Name
    ,Box = b.Name
FROM 
    @TEST_DATA d
        Inner join 
        cte_product p 
        left join cte_box b
        on
        p.Parent_Id = b.ID
        inner join cte_rack r
        on
        b.Parent_Id = r.ID
        inner join cte_shelf s
        on
        r.Parent_Id = s.ID
On
    d.ID = p.ID


select * from @TEST_DATA

Result

 ID          Parent_Id   Name                           Type       Shelf                          Rack                           Box
----------- ----------- ------------------------------ ---------- ------------------------------ ------------------------------ ------------------------------
39702       1664        0228MBDNAERA1                  P          Shelf 4                        Rack Q                         MBDNAERA1 - 3
39703       1664        0230MBDNAERA1                  P          Shelf 4                        Rack Q                         MBDNAERA1 - 3
39704       1664        0231MBDNAERA1                  P          Shelf 4                        Rack Q                         MBDNAERA1 - 3
39726       1744        0228MBDNAERA2                  P          Shelf 4                        Rack Q                         MBDNAERA2 - 3
39727       1744        0230MBDNAERA2                  P          Shelf 4                        Rack Q                         MBDNAERA2 - 3
39728       1744        0231MBDNAERA2                  P          Shelf 4                        Rack Q                         MBDNAERA2 - 3
39764       1752        0228MBDNAERA3                  P          Shelf 4                        Rack R                         MBDNAERA3 - 3
39766       1752        0230MBDNAERA3                  P          Shelf 4                        Rack R                         MBDNAERA3 - 3
39768       1752        0231MBDNAERA3                  P          Shelf 4                        Rack R                         MBDNAERA3 - 3
1744        1652        MBDNAERA2 - 3                  B                                                                        
1752        1732        MBDNAERA3 - 3                  B                                                                        
1664        1652        MBDNAERA1 - 3                  B                                                                        
1732        1617        Rack R                         R                                                                        
1652        1617        Rack Q                         R                                                                        
1617        2           Shelf 4                        S                                                                        
2           NULL        Freezer                        F

I am only trying to do a single UPDATE after the CTE, in which I would update the shelf, rack, and box values for that particular sample row. Is it possible to do this? — Selene, Aug 16 '17 at 15:31
The OUTPUT shows that nothing has been saved in the Loc_* columns. — Selene, Aug 16 '17 at 16:15
If you do not have different studies for the ID it is the expected result. — Maurício Pontalti Neri, Aug 16 '17 at 16:29
I just 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 and assign them to a single sample row? — Selene, Aug 16 '17 at 17:17
No, I want the row whose ID = sample ID to have all the levels of the tree down to the sample populated (shelf, rack, and row). I don't care about the rows containing IDs for shelf, rack, and row...they can be populated or not. I just want the sample paths to be complete. I'll post an example shortly.... — Selene, Aug 16 '17 at 19:23

Using CTE to update multiple columns in table and loop on all rows in table

1 Answers1