Trying to recursively select rows based on prior row parent in SQL

Question

Using the table ROLE(ROLE_ID, PARENT_ROLE_ID) how can I get all rows that are dependent on another? For example, a root level role will have a null value for [PARENT_ROLE_ID], but that root role could have many children, who in turn have many children. How can I go about handling this in a query? I keep coming across some options but not working. It is pretty much what oracle does when doing a 'start with' query. Any help would be greatly appreciated. The current example (and wrong) that I have is below:

select * into #Collection from MCCApps.dbo.APP_ROLE r where r.APP_ROLE_ID = 1

declare @ParentId int = 1

while exists (select * from #Collection WHERE APP_PARENT_ID = @ParentId)
begin

select @ParentId = (select top 1 @ParentId
                   from #Collection
                   order by APP_ROLE_ID asc)

DELETE FROM MCCApps.dbo.APP_ROLE
where APP_PARENT_ID = @ParentId

end

u want it, and its children and their children etc. or rather given `it` you want their hierachy above it — Drew, Jun 09 '15 at 20:39
You need a Recursive Common Table Expression (CTE). I can probably spin up a T-SQL (SQL Server variety) but I don't know if Oracle has their own implementation... — David W, Jun 09 '15 at 20:40
possible duplicate of [ROW\_NUMBER() in MySQL](http://stackoverflow.com/questions/1895110/row-number-in-mysql) — Anthony Horne, Jun 09 '15 at 20:41
@DrewPierce Yes, that is what I need to do, so that id the root level is deleted, all other levels will be deleted that are children. — Casey ScriptFu Pharr, Jun 09 '15 at 20:45
I need it for SQL. I am just mentiong what the Oracle way is, — Casey ScriptFu Pharr, Jun 09 '15 at 20:45
without focusing on your code, how far off is this statement: given a roleId, you want to delete the whole chain of everything that is below it, including grandchildren greatgrandchildren etc so that nothing is orphaned? — Drew, Jun 09 '15 at 20:54
Exactly right on. Apologies, as my code above is way off. I was trying to explain it correctly, but then did not want to post no code, and get yelled at. — Casey ScriptFu Pharr, Jun 09 '15 at 20:55

score 1 · Accepted Answer · answered Jun 09 '15 at 22:41

-- drop table role;
create table role
(   id int not null auto_increment primary key, -- for kicks and if i screw up
    roleId int not null,    -- i will manually provide it so i can see it
    parent int null,
    unique index (roleId)
);

insert role(roleId,parent) values (1,null),(2,null),(3,2),(4,3),(5,3),(6,3),(7,4),(8,3),(9,6),(10,6),(11,10);

-- if i want to delete roleId A then i need to delete anything with a parent=A (B) and all of B's lineage too

-- drop procedure deleteParentDownwards;
delimiter $$
create procedure deleteParentDownwards
(
deleteMe int
)
BEGIN
    -- deleteMe parameter means i am anywhere in hierarchy of role
    -- and i want me and all my offspring deleted (no orphaning of children or theirs)
    declare bDoneYet boolean default false;
    declare working_on int;
    declare theCount int;

    CREATE TABLE xxDeleteRoleHierarchyxx
    (
        roleId int not null,
        processed int not null
    );

    set bDoneYet=false;
    insert into xxDeleteRoleHierarchyxx (roleId,processed) select deleteMe,0;
    while (!bDoneYet) do
        select count(*) into theCount from xxDeleteRoleHierarchyxx where processed=0;

        if (theCount=0) then 
            -- found em all
            set bDoneYet=true;
        else
            -- one not processed yet, insert its children for processing
            SELECT roleId INTO working_on FROM xxDeleteRoleHierarchyxx where processed=0 limit 1;
            insert into xxDeleteRoleHierarchyxx (roleId,processed)
            select roleId,0 from role
            where parent=working_on;

            -- mark the one we "processed for children" as processed
            update xxDeleteRoleHierarchyxx set processed=1 where roleId=working_on;
        end if;
    end while;
    delete from role where roleId in (select roleId from xxDeleteRoleHierarchyxx);
    drop table xxDeleteRoleHierarchyxx;
END
$$

call deleteParentDownwards(3); -- deletes many

call deleteParentDownwards(6); -- deletes 4 (assuming line above was not called first!)

Trying to recursively select rows based on prior row parent in SQL

1 Answers1

Linked