August 16, 2017 at 7:17 am
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 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 = 'CRIB'
-- WHERE Study_ID = 'CRIB'
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, it loops forever ('P' indicates sample rows, so I am only traversing the tree for them):
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!
August 16, 2017 at 7:48 am
There seems to be something missing there, but I'm not sure what would it be without sample data. I wrote an article about a similar problem some time ago, it might help you.
http://www.sqlservercentral.com/articles/set-based+loop/127670/
August 16, 2017 at 8:08 am
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)
INSERT INTO SimpleH (ID, ParentID, [Name]) VALUES (1, NULL, 'Shelf1')
INSERT INTO SimpleH (ID, ParentID, [Name]) VALUES (2, 1, 'Rack1 Shelf1')
INSERT INTO SimpleH (ID, ParentID, [Name]) VALUES (3, 1, 'Rack2 Shelf1')
INSERT INTO SimpleH (ID, ParentID, [Name]) VALUES (4, 1, 'Rack3 Shelf1')
INSERT INTO SimpleH (ID, ParentID, [Name]) VALUES (5, 2, 'Box1 Rack1 Shelf1')
INSERT INTO SimpleH (ID, ParentID, [Name]) VALUES (6, 2, 'Box2 Rack1 Shelf1')
INSERT INTO SimpleH (ID, ParentID, [Name]) VALUES (7, 3, 'Box1 Rack2 Shelf1')
INSERT INTO SimpleH (ID, ParentID, [Name]) VALUES (8, 3, 'Box2 Rack2 Shelf1')
INSERT INTO SimpleH (ID, ParentID, [Name]) VALUES (9, 4, 'Box1 Rack3 Shelf1')
INSERT INTO SimpleH (ID, ParentID, [Name]) VALUES (10, 4, 'Box2 Rack3 Shelf1')
INSERT INTO SimpleH (ID, ParentID, [Name]) VALUES (11, NULL, 'Shelf2')
INSERT INTO SimpleH (ID, ParentID, [Name]) VALUES (12, 11, 'Rack1 Shelf2')
INSERT INTO SimpleH (ID, ParentID, [Name]) VALUES (13, 11, 'Rack2 Shelf2')
INSERT INTO SimpleH (ID, ParentID, [Name]) VALUES (14, 11, 'Rack3 Shelf2')
INSERT INTO SimpleH (ID, ParentID, [Name]) VALUES (15, 12, 'Box1 Rack1 Shelf2')
INSERT INTO SimpleH (ID, ParentID, [Name]) VALUES (16, 12, 'Box2 Rack1 Shelf2')
INSERT INTO SimpleH (ID, ParentID, [Name]) VALUES (17, 13, 'Box1 Rack2 Shelf2')
INSERT INTO SimpleH (ID, ParentID, [Name]) VALUES (18, 13, 'Box2 Rack2 Shelf2')
INSERT INTO SimpleH (ID, ParentID, [Name]) VALUES (19, 14, 'Box1 Rack3 Shelf2')
INSERT INTO SimpleH (ID, ParentID, [Name]) VALUES (20, 14, 'Box2 Rack3 Shelf2') For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 16, 2017 at 9:51 am
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;
August 16, 2017 at 12:37 pm
Are you looking for something like this?
WITH rCTE AS (
SELECT [NewLevel] = 1,
ID,
ParentID,
[Name],
[Name] AS shelf,
rack,
box
FROM dbo.SimpleH
WHERE ParentID IS NULL
UNION ALL
SELECT [NewLevel] = ls.[NewLevel]+1,
cs.ID,
cs.ParentID,
cs.[Name],
ls.shelf,
CASE WHEN ls.[NewLevel] = 1 THEN cs.Name ELSE ls.rack END,
CASE WHEN ls.[NewLevel] = 2 THEN cs.Name ELSE ls.box END
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;
Once again, have you read the article that I showed you? If you're updating the whole table, it will be a much faster way to get it.
August 16, 2017 at 1:18 pm
It's close...is it possible assign 'Name' to either box, rack, or shelf depending on what type (level) it is? The code below now saves the next level up (e.g. box for samples, rack for boxes, etc.) but no higher. I'm so close! I just need to save all levels of the hierarchy in the sample row:
WITH cte AS
(
SELECT ID,
Parent_Id,
Name,
Study_ID,
Loc_Box,
Loc_Shelf,
Loc_Rack,
Loc_Type
FROM tempspec
WHERE Study_ID = 'CRIB'
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
)
UPDATE E1
SET Loc_Box = CASE WHEN E2.Loc_Type = 'B' THEN E2.Name ELSE E2.Loc_Box END,
Loc_Rack = CASE WHEN E2.Loc_Type = 'R' THEN E2.Name ELSE E2.Loc_Rack END,
Loc_Shelf = CASE WHEN E2.Loc_Type = 'S' THEN E2.Name ELSE E2.Loc_Shelf END
OUTPUT DELETED.* , INSERTED.*
FROM tempspec E1
LEFT JOIN cte E2
ON E1.parent_id = E2.ID;
August 16, 2017 at 1:46 pm
You need to check your rCTE. You're not using its capabilities and could simply use the table without CTE for your update. Read the examples that Chris and I posted previously.
August 16, 2017 at 1:50 pm
I have read them but they're confusing to me...Above, you said "If you're updating the whole table, it will be a much faster way to get it." -- WHICH way do you mean? You offer several solutions...
I don't know which option is best based on what I need to produce. This is why I posted on the forum, hoping someone could tell me exactly how to modify the code to work correctly. Thanks.
August 17, 2017 at 4:06 am
-- Amended sample set with full name
DROP TABLE dbo.SimpleH;CREATE TABLE dbo.SimpleH (ID INT NOT NULL PRIMARY KEY CLUSTERED, ParentID INT NULL, [FullName] VARCHAR(30) NOT 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, [FullName], [Name]) VALUES (1, NULL, 'Shelf1', 'Shelf1')
INSERT INTO SimpleH (ID, ParentID, [FullName], [Name]) VALUES (2, 1, 'Rack1 Shelf1', 'Rack1')
INSERT INTO SimpleH (ID, ParentID, [FullName], [Name]) VALUES (3, 1, 'Rack2 Shelf1', 'Rack2')
INSERT INTO SimpleH (ID, ParentID, [FullName], [Name]) VALUES (4, 1, 'Rack3 Shelf1', 'Rack3')
INSERT INTO SimpleH (ID, ParentID, [FullName], [Name]) VALUES (5, 2, 'Box1 Rack1 Shelf1', 'Box1')
INSERT INTO SimpleH (ID, ParentID, [FullName], [Name]) VALUES (6, 2, 'Box2 Rack1 Shelf1', 'Box2')
INSERT INTO SimpleH (ID, ParentID, [FullName], [Name]) VALUES (7, 3, 'Box1 Rack2 Shelf1', 'Box1')
INSERT INTO SimpleH (ID, ParentID, [FullName], [Name]) VALUES (8, 3, 'Box2 Rack2 Shelf1', 'Box2')
INSERT INTO SimpleH (ID, ParentID, [FullName], [Name]) VALUES (9, 4, 'Box1 Rack3 Shelf1', 'Box1')
INSERT INTO SimpleH (ID, ParentID, [FullName], [Name]) VALUES (10, 4, 'Box2 Rack3 Shelf1', 'Box2')
INSERT INTO SimpleH (ID, ParentID, [FullName], [Name]) VALUES (11, NULL, 'Shelf2','Shelf2')
INSERT INTO SimpleH (ID, ParentID, [FullName], [Name]) VALUES (12, 11, 'Rack1 Shelf2','Rack1')
INSERT INTO SimpleH (ID, ParentID, [FullName], [Name]) VALUES (13, 11, 'Rack2 Shelf2','Rack2')
INSERT INTO SimpleH (ID, ParentID, [FullName], [Name]) VALUES (14, 11, 'Rack3 Shelf2','Rack3')
INSERT INTO SimpleH (ID, ParentID, [FullName], [Name]) VALUES (15, 12, 'Box1 Rack1 Shelf2','Box1')
INSERT INTO SimpleH (ID, ParentID, [FullName], [Name]) VALUES (16, 12, 'Box2 Rack1 Shelf2','Box2')
INSERT INTO SimpleH (ID, ParentID, [FullName], [Name]) VALUES (17, 13, 'Box1 Rack2 Shelf2','Box1')
INSERT INTO SimpleH (ID, ParentID, [FullName], [Name]) VALUES (18, 13, 'Box2 Rack2 Shelf2','Box2')
INSERT INTO SimpleH (ID, ParentID, [FullName], [Name]) VALUES (19, 14, 'Box1 Rack3 Shelf2','Box1')
INSERT INTO SimpleH (ID, ParentID, [FullName], [Name]) VALUES (20, 14, 'Box2 Rack3 Shelf2','Box2') For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply