代码之家 › 专栏 › 技术社区 › Greg Gum

如何更新hierarchyid列

sql-server-2012 tsql sql-server

Greg Gum · 技术社区 · 6 年前

我有一张桌子叫 items

我也跟着 this 这样做的教程。

除了最后的update语句外,本教程的所有步骤都有效。

我收到错误消息:

不允许从数据类型hierarchyid隐式转换为nvarchar(max)。使用CONVERT函数运行此查询。

drop table #children
CREATE TABLE #Children   
   (  
    ID int,  
    TenantId int,
    ParentID int,
    Num int  
);  
GO
CREATE CLUSTERED INDEX tmpind ON #Children(TenantId, ParentID, ID);  
GO

INSERT #Children (ID,  TenantId,ParentID, Num)  
SELECT ID, TenantId, ParentID, 
  ROW_NUMBER() OVER (PARTITION BY TenantId, ParentID ORDER BY ParentId)   
FROM Items  
GO 

SELECT * FROM #Children ORDER BY TenantId, ParentID, Num  
GO


WITH paths(path, ID, ParentId, TenantId)   
AS (  
-- This section provides the value for the root of the hierarchy  
SELECT hierarchyid::GetRoot() AS OrgNode, ID, ParentId, TenantId   
FROM #Children AS C   
WHERE ParentId IS NULL   

UNION ALL   
-- This section provides values for all nodes except the root  
SELECT   
CAST(p.path.ToString() + CAST(C.Num AS varchar(30)) + '/' AS hierarchyid),   
C.ID , C.ParentId, C.TenantId 
FROM #Children AS C   
JOIN paths AS p   
   ON C.ParentID = P.ID   
)
-- This select statement runs just fine and shows expected data.
--Select i.Id as ItemId, p.path, p.path.ToString() as LogicalNode, p.Id, p.ParentId, p.TenantId from Paths P
--join Items I on p.Id = i.Id
--order by P.TenantId, P.path

--Note that I have tried using the convert function, but it still fails with the same error message.
UPDATE I Set OrgNode = Convert(hierarchyid, P.path)
FROM Items I    
JOIN Paths AS P   
   ON I.ID = P.ID  
GO

编辑奇怪的是,这个 DBFiddle 作品。

2 回复 | 直到 6 年前

Lukasz Szozda 6 年前

OrgNode 不是类型 hierachyid . 你可以用 ToString()

UPDATE I Set OrgNode = P.path.ToString()
FROM Items I    
JOIN Paths AS P   
   ON I.ID = P.ID

或更改表格 Items 并更改列类型。

Ben Thul 6 年前

看起来你解决了你的问题,但我建议保存到最后的hierarchyid转换。这样地:

WITH paths(path, ID, ParentId, TenantId)   
AS (  
-- This section provides the value for the root of the hierarchy  
SELECT cast('/' as varchar(max)) AS OrgNode, ID, ParentId, TenantId   
FROM #Children AS C   
WHERE ParentId IS NULL   

UNION ALL   
-- This section provides values for all nodes except the root  
SELECT   
CAST(concat(p.path.ToString(), C.Num, '/') AS varchar(max)),   
C.ID , C.ParentId, C.TenantId 
FROM #Children AS C   
JOIN paths AS p   
   ON C.ParentID = P.ID   
)
-- This select statement runs just fine and shows expected data.
--Select i.Id as ItemId, p.path, p.path.ToString() as LogicalNode, p.Id, p.ParentId, p.TenantId from Paths P
--join Items I on p.Id = i.Id
--order by P.TenantId, P.path

--Note that I have tried using the convert function, but it still fails with the same error message.
UPDATE I Set OrgNode = Convert(hierarchyid, P.path)
FROM Items I    
JOIN Paths AS P   
   ON I.ID = P.ID  
GO

注意,我还更改了 + 连接的样式 concat() 函数,这样就不必在转换时乱来 C.Num 给一个瓦查尔。