删除递归子项

Question

我有以下sql，可以让我获得根论坛帖子的所有子孙。

with recursive all_posts (id, parentid, root_id) as
                (
                select t1.id,
                t1.parent_forum_post_id as parentid,
                t1.id as root_id
                from forumposts t1
                union all
                select c1.id,
                c1.parent_forum_post_id as parentid,
                p.root_id
                from forumposts
                c1
                join all_posts p on p.id = c1.parent_forum_post_id
                )
                select fp.id
                from forumposts fp inner join all_posts ap
                on fp.id=ap.id 
                where
                root_id=1349 
                group by fp.id

问题是我希望删除所选记录。类似于从论坛帖子 fp 中删除的东西，其中 fp.id=（最后从上面的代码中选择），但这不起作用（我在"DELETE"或附近收到语法错误）。这是我第一次使用递归，我一定错过了什么。任何帮助，不胜感激。

Answer 1

您可以简单地使用 DELETE 语句而不是 SELECT 来完成您的工作：

with recursive all_posts (id, parentid, root_id) as (
    select t1.id,
    t1.parent_forum_post_id as parentid,
    t1.id as root_id
    from forumposts t1
    union all
    select c1.id,
    c1.parent_forum_post_id as parentid,
    p.root_id
    from forumposts
    c1
    join all_posts p on p.id = c1.parent_forum_post_id
)
DELETE FROM forumposts
 WHERE id IN (SELECT id FROM all_posts WHERE root_id=1349);

其他可能的组合，例如根据子表中删除的行从主表中删除，请查看文档。

编辑：对于9.1之前的PostgresSQL版本，您可以使用这样的初始查询：

DELETE FROM forumposts WHERE id IN (
    with recursive all_posts (id, parentid, root_id) as (
        select t1.id,
        t1.parent_forum_post_id as parentid,
        t1.id as root_id
        from forumposts t1
        union all
        select c1.id,
        c1.parent_forum_post_id as parentid,
        p.root_id
        from forumposts c1
        join all_posts p on p.id = c1.parent_forum_post_id
    )
    select id from all_posts ap where root_id=1349
);