SQLite递归处理层级关键词关联记录删除的问题求助

现有两张用于实现图片层级关键词标签系统的表，无法修改其设计结构，表结构如下：

Keywords Table                    ItemsKeywords Table

Id | Name        | ParentId       ItemId | KeywordId
---+-------------+---------       -------+----------
1  | Birds       | NULL           1      | 1
2  | Owls        | 1              1      | 2
3  | Barred Owl  | 2              1      | 4
4  | White Stork | 2              2      | 1
5  | Storks      | 1              2      | 2
6  | Wood Stork  | 5              2      | 3
7  | White Stork | 5              2      | 4
                                  3      | 1
                                  3      | 2
                                  3      | 6

规则说明：

• 若关键词与项目关联，则该关键词的所有父关键词也需关联该项目；
• 若解除关键词与项目的关联，则其所有父关键词也需解除关联，但仅当父关键词无其他关联同一项目的子关键词时才可执行。

我编写了如下递归CTE代码，用于筛选可从ItemsKeywords表中删除的记录，但当前实现陷入无限循环，问题在于不知道如何仅基于递归上一轮添加的记录来新增记录，另外也不确定LAG()函数是否必要，恳请帮助：

WITH

  /*
   * Create a table of Id, ParentId where we start with a
   * given Id and then recursively add each parent keyword as a new
   * Id, ParentId record.
   */

  RECURSIVE hierarchyTable AS (
    SELECT Id AS KeywordId, ParentId FROM Keywords WHERE Id = 4
    UNION ALL
    SELECT Keywords.Id AS KeywordId, Keywords.ParentId FROM Keywords, hierarchyTable WHERE hierarchyTable.ParentID = Keywords.Id
  ),

  /*
   * Create a table of all items association with a given keyword.
   */

  assocItemsTable AS (
      SELECT ItemId FROM ItemsKeywords WHERE KeywordId = 4
  ),

  /*
   * We combine the hierarchy table with the associated items
   * table to create a set of all the potential associations
   * between the item and a given keyword plus all its parents.
   */

  completeAssociations AS (
      SELECT
    assocItemsTable.ItemId,
    hierarchyTable.KeywordId,
    hierarchyTable.ParentId
      FROM assocItemsTable
      CROSS JOIN hierarchyTable
  ),

  /*
   * Create a table that includes one record each time the
   * record's keyword is a child of a record in the complete
   * associations table.
   */

  children AS (
    SELECT ItemId, KeywordId FROM Keywords
    JOIN ItemsKeywords
    ON Id = KeywordId AND ItemId = completeAssociations.ItemId AND ParentId = completeAssociations.KeywordId
  ),

  /*
   * For each entry in the complete associations table, add the
   * number of children of each keyword that is also one of the
   * complete associations.
   */

  childTable AS (
    SELECT
      ItemId,
      LAG(ItemId) OVER () AS LastItemId,
      KeywordId,
      ParentId,
      LAG(ParentId) OVER () AS LastParentId,
      ( SELECT COUNT() FROM children ) AS ChildCount

    /*
     * The CROSS JOIN below creates a table of ItemId, KeywordId where
     * the keyword is a given keyword ID or any parent keyword ID
     * permuted against any item IDs associated with the given keyword.
     * This table lists all the possible associations for a keyword with
     * an item when we have a complete hierarchy.
     */

    FROM completeAssociations
  ),

  x AS (
    SELECT ItemId, KeywordId, ParentId, ChildCount FROM childTable WHERE LastParentId IS NULL

    UNION ALL

    SELECT x.ItemId, x.KeywordId, x.ParentId, x.ChildCount FROM x
    JOIN childTable ON childTable.ItemId = x.ItemId AND childTable.KeywordId = x.ParentId
    WHERE childTable.ChildCount < 2
  )

SELECT * FROM x;

内容的提问来源于stack exchange，提问作者freixas