Adjacency list model duplicate parent & children

Hierarchical Data in MySQL Using the Adjacency List Model

I have this table named node_structur_data

CREATE TABLE node_structure_data (
   id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
   title VARCHAR(455) NOT NULL,
   parent_id INT(10) UNSIGNED DEFAULT NULL,
   PRIMARY KEY (id),
   FOREIGN KEY (parent_id) REFERENCES node_structure_data (id)
   ON DELETE CASCADE ON UPDATE CASCADE
);

Output:

id  title   parent_id
1   Division  NULL
2   Site 1    1
3   Paper     2
4   ms1       3

How can I duplicate a node and its children?
For example Site 1
The id & parent_id should be unique but the title should stay the same.

Expected Output:

id  title   parent_id
1   Division  NULL
2   Site 1    1
3   Paper     2
4   ms1       3
5   Site 1    1
6   Paper     5
7   ms1       6

Solution

The following approach first estimates the new max and then uses a recursive cte to find all children of the desired node 'Site 1' and determine their new possible parent_id if there were no other concurrent writes to the table.

I would recommend running the following in a transaction and locking the table during the operation to prevent concurrent table modifications.

To test this approach I added some additional sample data which I have included below, however you may see the approach in a demo with your initial sample data here

See output of working db fiddle below:

Schema (MySQL v8.0)

CREATE TABLE node_structure_data (
   id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
   title VARCHAR(455) NOT NULL,
   parent_id INT(10) UNSIGNED DEFAULT NULL,
   PRIMARY KEY (id),
   FOREIGN KEY (parent_id) REFERENCES node_structure_data (id)
   ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO node_structure_data
  (`id`, `title`, `parent_id`)
VALUES
  ('1', 'Division', NULL),
  ('2', 'Site 1', '1'),
  ('3', 'Paper', '2'),
  ('4', 'ms1', '3'),
  ('5', 'ms2', '3'),
  ('6', 'os1', '4'),
  ('7', 'os2', '4'),
  ('8', 'gs1', '1'),
  ('9', 'hs1', '3'),
  ('10','js1','9');

Query #1

select 'Before Insert';

Before Insert
Before Insert

Query #2

select * from node_structure_data;

id	title	parent_id
1	Division
2	Site 1	1
3	Paper	2
4	ms1	3
5	ms2	3
6	os1	4
7	os2	4
8	gs1	1
9	hs1	3
10	js1	9

Query #3

select 'Possible Data Changes';

Possible Data Changes
Possible Data Changes

Query #4

with recursive max_id AS (
    SELECT MAX(id) as id FROM node_structure_data
),
child_nodes AS (
    SELECT
        n.id,
        title,
        parent_id,
        m.id+1 as new_id,
        parent_id as new_parent_id
    FROM
        node_structure_data n
    CROSS JOIN
        max_id as m
    WHERE
        title='Site 1'
    
  
    UNION ALL
  
    SELECT
        n.id,
        n.title,
        n.parent_id,
        @row_num:=IF(@row_num=0,c.new_id,0) + 1 + @row_num  as new_id,
        c.new_id
    FROM
        child_nodes c
    INNER JOIN
        node_structure_data n ON n.parent_id = c.id 
    CROSS JOIN (
        SELECT @row_num:=0 as rn
    ) as vars
    
)
SELECT * FROM child_nodes;

id	title	parent_id	new_id	new_parent_id
2	Site 1	1	11	1
3	Paper	2	12	11
4	ms1	3	13	12
5	ms2	3	14	12
9	hs1	3	15	12
6	os1	4	16	13
7	os2	4	17	13
10	js1	9	18	15

Query #5 - Performing actual insert

INSERT INTO node_structure_data (title,parent_id)
with recursive max_id AS (
    SELECT MAX(id) as id FROM node_structure_data
),
child_nodes AS (
    SELECT
        n.id,
        title,
        parent_id,
        m.id+1 as new_id,
        parent_id as new_parent_id
    FROM
        node_structure_data n
    CROSS JOIN
        max_id as m
    WHERE
        title='Site 1'
    
  
    UNION ALL
  
    SELECT
        n.id,
        n.title,
        n.parent_id,
        @row_num:=IF(@row_num=0,c.new_id,0) + 1 + @row_num  as new_id,
        c.new_id
    FROM
        child_nodes c
    INNER JOIN
        node_structure_data n ON n.parent_id = c.id 
    CROSS JOIN (
        SELECT @row_num:=0 as rn
    ) as vars
    
)
SELECT title,new_parent_id FROM child_nodes ORDER BY new_id;

There are no results to be displayed.

Query #6

select 'AFTER INSERT';

AFTER INSERT
AFTER INSERT

Query #7

select * from node_structure_data;

id	title	parent_id
1	Division
2	Site 1	1
3	Paper	2
4	ms1	3
5	ms2	3
6	os1	4
7	os2	4
8	gs1	1
9	hs1	3
10	js1	9
11	Site 1	1
12	Paper	11
13	ms1	12
14	ms2	12
15	hs1	12
16	os1	13
17	os2	13
18	js1	15

View on DB Fiddle

Let me know if this works for you.