sql-server - 根据搜索文本删除父子记录

Question

根据我的应用程序中的搜索文本，我必须按照以下格式从表中删除父记录和子记录:

tabItem

+--------+--------------+----------+-----------------+
| ItemId | ItemParentId | ItemName | ItemDescription |
+--------+--------------+----------+-----------------+

如果我在我的应用程序内的文本框中键入例如“信息”并单击“过滤器”，如果树中的最低项包含“信息”，我必须删除所有父项和子项。

更好解释的树:

Category 1
|
+--- Subcategory
     |
     +--- Subsubcategory
          |
          +--- Item (contains "information" in ItemDescription)
Category 2
|
+--- Subcategory
     |
     +--- Subsubcategory
          |
          +--- Item (doesn't contain "information")

现在我必须删除包含“信息”的项目及其所有 parent 和祖 parent 。

我尝试使用以下 cte:

WITH cte_toDelete
AS
(
    SELECT *
    FROM tabItem
    UNION ALL
    SELECT cte_toDelete.*
    FROM cte_toDelete
    INNER JOIN tabItem ON cte_toDelete.ItemParentId = tabItem.ItemId
)
DELETE FROM tabItem
WHERE ItemId IN
(
    SELECT ItemId
    FROM cte_toDelete
    WHERE cte_toDelete.ItemName NOT LIKE '%' + @SearchText + '%'
    AND cte_toDelete.ItemDescription NOT LIKE '%' + @SearchText + '%'
)

但是当我运行这些行时，出现以下错误:

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

我的 cte 有什么问题？

Answer 1

CTE allows The maximum 100 recursion only

我们可以使用 MAXRECURSION 更改它的设置，MAXRECURSION 的值可以在 0 到 32,767 之间

了解更多信息 MAXRECURSION

WITH cte_toDelete
AS
(
   SELECT *
   FROM tabItem
   UNION ALL
   SELECT cte_toDelete.*
   FROM cte_toDelete
   INNER JOIN tabItem ON cte_toDelete.ItemParentId = tabItem.ItemId
)
DELETE FROM tabItem
WHERE ItemId IN
(
  SELECT ItemId
  FROM cte_toDelete
  WHERE cte_toDelete.ItemName NOT LIKE '%' + @SearchText + '%'
  AND cte_toDelete.ItemDescription NOT LIKE '%' + @SearchText + '%'
 )
 OPTION (MAXRECURSION 0)