How can I use IF and CONCAT within SELECT

I have this Adjacency List Model table

Table:

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
5   Site 2          1
6   Paper           5
7   ms2             6
8   Site 3          1
9   Paper           8
10  ms3             9

So I have the following query that duplicates a Site 1 e.g. and its children.
In this case, the children are Paper with parent_id = 2 and ms1 with parent_id = 3

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.parent_id,
        n.title,
        @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;

Output:

    id  title   parent_id
1   Division         NULL
2   Site 1              1
3   Paper               2
4   ms1                 3
5   Site 2              1
6   Paper               5
7   ms2                 6
8   Site 3              1
9   Paper               8
10  ms3                 9
11  Site 1              1
12  Paper              11
13  ms1                12

As you can see Site 1 and its children got duplicated with a new unique id.
However for the duplicated Site title I want to have a prefix text Copy of for the DUPLICATED Site 1 title
I only want that prefix for a Site/parent_id = 1

So that the duplicated nodes should look like this:

    id  title   parent_id
1   Division         NULL
2   Site 1              1
3   Paper               2
4   ms1                 3
5   Site 2              1
6   Paper               5
7   ms2                 6
8   Site 3              1
9   Paper               8
10  ms3                 9
11  Copy of Site 1      1
12  Paper              11
13  ms1                12

I have tried to implement the IF and CONCAT in the query but for some reason, it doesn’t work, I don’t get any errors but the output stays the same.

IF(n.title LIKE '%Site%', CONCAT("Copy of ", n.title), n.title),

If the title contains the text Site then I want to contact the prefix and the site title otherwise no concat.

Any ideas?

Any help is appreciated!!!

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

This solution shows how to insert a copy of a subtree and re-identify the descendants.

INSERT INTO node_structure_data (id, title, parent_id)
WITH RECURSIVE subtree AS (
  SELECT 
    id,
    (SELECT MAX(id) FROM node_structure_data) AS last_id,
    CONCAT('Copy of ', title) AS title, 
    parent_id
  FROM node_structure_data
  WHERE id = 2 -- i.e. title = 'Site 1'
  UNION ALL
  SELECT 
    n.id, 
    s.last_id,
    n.title, 
    n.parent_id
  FROM subtree s
  JOIN node_structure_data n ON s.id = n.parent_id
  
), new_id AS (
  SELECT 
    id, 
    last_id + ROW_NUMBER() OVER (ORDER BY id) AS new_id, 
    title, 
    parent_id
  FROM subtree
)
SELECT 
  n.new_id AS id,
  n.title,
  COALESCE(p.new_id, n.parent_id) AS parent_id
FROM new_id n
LEFT JOIN new_id p ON n.parent_id = p.id

Note that starting MySQL 8 setting user variables within expressions is deprecated and will be removed in a future release.

The following fiddle shows the results of each CTE – db<>fiddle

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0