How to copy a node's children in an adjacent list

Question

I have an adjacent list hierarchy model that makes up a topic structure

   ID   Parent_Id Topic_Name
   1    Null      Topic 1
   2    Null      Topic 2
   3    2            Topic 3
   4    3               Topic 4
   5    2            Topic 5
   6    Null      Topic 6

I want to specify a topic id and then copy it to a new topic id at a certain position and retain the levels / structure underneath

So in my example I could specify topic topic_id 2 with pos_id 1 and it would create

   ID   Parent_Id Topic_Name
   1    Null      Topic 1
   7    Null      Topic 2
   8    7            Topic 3
   9    8               Topic 4
   10    7            Topic 5
   2    Null      Topic 2
   3    2            Topic 3
   4    3               Topic 4
   5    2            Topic 5
   6    Null      Topic 6

topic_id being the node to copy and pos_id is the node to insert the copy after

Auto numbering is on for the ID, but I can't guarantee that subnodes will always be the next id number up from the parent.

topic_id being the node to copy and pos_id is the node to insert the copy after

Do you want to duplicate a topic (including the children) or you want to change the parent for some topic? — Bogdan Sahlean, Jul 26 '13 at 11:07
I tried this solution here but it assumes children will have a squential id from their parent I think http://stackoverflow.com/questions/12706956/is-there-a-way-to-duplicate-part-of-a-adjacent-list-hierarchy-given-a-starting-i — totalitarian, Jul 26 '13 at 11:08

score 1 · Answer 1 · answered Jul 26 '13 at 11:01

1

You might want to have a look at Nested Treesets wich would be way better for your purpose I think.

Great explanation here:

http://en.wikipedia.org/wiki/Nested_set_model

answered Jul 26 '13 at 11:01

MMMagic

182
2
14

I did look at the model but had issues with performance. With inserts and reordering, the left and right values have to be updated for every record. On a table with >1m rows, this is a painful process – totalitarian Jul 26 '13 at 11:05

score 1 · Accepted Answer · answered Jul 26 '13 at 13:35

I think you can do this in a single statement. Here is the idea.

First, expand the data for all parents (at whatever level) for each id. This uses a recursive CTE.

Then, go back to the original list and choose only those who are descendants of 2.

Then assign a new id to each of the ids found in this group. The following query gets that maximum id and adds a row_number() constant to it.

Then, for each record in the subtree, lookup the new id's in the record, and then insert the results.

The following query takes this approach. I haven't tested it:

with Parents as (
      select id, parent_id, 1 as level
      from AdjList al
      union all
      select cte.id, cte.Parent_id, level+1
      from AdjList al join
           cte
           on cte.Parent_id = al.id
     ),
     LookingFor as (
      select *
      from AdjList
      where id in (select id from Parents where id = 2)
     ),
     NewIds as (
      select id, const.maxid + ROW_NUMBER() over (order by (select NULL)) as newid
      from (select distinct id
            from LookingFor
           ) t cross join
           (select MAX(id) as maxid, from AdjList) const
     )
insert into AdjList(Id, Parent_id, Topic_Name)
    select ni1.newid, coalesce(ni2.NEWID, 1), lf.Topic_Name
    from LookingFor lf left outer join
         NewIds ni1
         on lf.id = ni1.id left outer join
         NewIds ni2
         on lf.Parent_Id = ni2.id
    where ni1.newid is not null

How to copy a node's children in an adjacent list

2 Answers2

Linked