sql-server - 如何更新hierarchyid列

Question

我有一个名为的表items，它有一个父/子关系，我将其转换为一个层次结构。

我已按照本教程这样做。

本教程的所有步骤都有效，除了最后的更新语句。

我收到错误消息：

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

但这没有任何意义。我正在更新的字段是一个hierarchyid，而不是一个nvarchar(nax)。所以我看不到哪里涉及到 nvarchar(max) 字段。

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有效。

标签： sql-servertsqlsql-server-2012

Answer 1

看起来 columnOrgNode不是 type hierachyid。你可以使用ToString()

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

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