I have a Table called Item. The design is like:
我有一个名为Item的表。设计如下:
ItemTable[
ItemID int,
ItemName varchar(50),
ParentItemId int ]
What I want is to find all the items that have an ancestor id and do some operation (e.g. delete, update).
我想要的是找到所有具有祖先id并执行某些操作的项目(例如删除,更新)。
Say I have six items,
说我有六件物品,
For Item 1, the parentItemId is null,
for Item 2, the parentId is 1,
for Item 3, the parentId is 1
Item 4, parent id 2,
item 5, parent id 2,
item 6, parent id 4
I want to find all items that has an ancestor id 2. So should be 4,5, and 6.
我想找到所有具有祖先id 2的项目。所以应该是4,5和6。
In sql world, I can do it in a cursor. But wondering if it is something can be done using a clever CTE?
在sql世界中,我可以在游标中完成它。但是想知道它是否可以使用聪明的CTE来完成?
1 个解决方案
#1
3
You can use recursive CTE:
您可以使用递归CTE:
;WITH cte(ItemID, ItemName,ParentItemId) AS (
SELECT ItemID, ItemName, ParentItemId
FROM #ItemTable
WHERE ParentItemId IS NOT NULL
UNION ALL
SELECT i.ItemID, i.ItemName, t.ParentItemId
FROM cte t
JOIN #ItemTable i
ON t.ItemID = i.ParentItemId
), cte2 AS -- filter records with Ancestor = 2
(
SELECT *
FROM cte
WHERE ParentItemId = 2
)
UPDATE #ItemTable -- UPDATE/DELETE operation
SET ItemName = CONCAT('Updated Name: ', i1.ItemName)
FROM #ItemTable i1
JOIN cte2 c2
ON i1.ItemID = c2.ItemId;
SELECT *
FROM #ItemTable;
LiveDemo
#1
3
You can use recursive CTE:
您可以使用递归CTE:
;WITH cte(ItemID, ItemName,ParentItemId) AS (
SELECT ItemID, ItemName, ParentItemId
FROM #ItemTable
WHERE ParentItemId IS NOT NULL
UNION ALL
SELECT i.ItemID, i.ItemName, t.ParentItemId
FROM cte t
JOIN #ItemTable i
ON t.ItemID = i.ParentItemId
), cte2 AS -- filter records with Ancestor = 2
(
SELECT *
FROM cte
WHERE ParentItemId = 2
)
UPDATE #ItemTable -- UPDATE/DELETE operation
SET ItemName = CONCAT('Updated Name: ', i1.ItemName)
FROM #ItemTable i1
JOIN cte2 c2
ON i1.ItemID = c2.ItemId;
SELECT *
FROM #ItemTable;
LiveDemo