Fixing broken integrity hierarchy trees using SQL

Question

I have a basic parent child table (parentid int, childid int) for groups where top level groups have a parentid of -1. I would like to make sure that changes in the org structure did not break the chain, and if they did, fix it. Meaning if there is a child-parent record for which the top level parentid is not -1, then we need to fix it and change its parentid to -1.

For example:

insert into tbl_x parentid, childid (-1,1), (-1,2), (2,3), (2,4), (2,5), (6,7), (7,8)

would result in groups 1-5 all ending up with -1 at the top, but group 7 needs to be fixed because it ends up with 6 at the top level - therefore the link was somehow broken and needs to be fixed from (6,7) to (-1,7) to bring group 7 to the top.

Answer 1

So you need to create a set of rows for those parentids that are not themselves childids?

INSERT INTO tbl_x (parentid, childid)
SELECT -1, parentid as childid
FROM tbl_x
WHERE parentid NOT IN (SELECT childid FROM tbl_x)

I think that should do it.