CODEWITHSUNDEEP
sql-server
totalitarian
totalitarian

Reputation: 3676

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

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

Upvotes: 1

Views: 112

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

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

Upvotes: 1

MMMagic
MMMagic

Reputation: 182

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

Upvotes: 1

Related Questions