删除递归子级 [英] Delete recursive children

问题描述

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

I have the following sql that gets me all the children and grandchildren of a root forumpost.

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处或附近出现语法错误）。这是我第一次使用递归，而且我一定缺少一些东西。

Thing is I want the records selected to be deleted. Something like delete from forumposts fp where fp.id=(last select from the code above) but that doesn't work(I get syntax error at or near "DELETE"). This is my first time ever using recursive and I must be missing something. Any help is appreciated.

推荐答案

您只需使用 DELETE 语句即可。代替 SELECT 来完成工作：

You can simply use the DELETE statement instead of SELECT to do your job:

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);

其他可能的组合，例如基于子级中被删除的行从主表中删除，查看文档。

Other combinations possible, like deleting from master table based on the deleted rows in the child one, check out the documentation.

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

For PostgresSQL versions prior to 9.1, you can use your initial query like this:

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
);

这篇关于删除递归子级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！