How can I use IF and CONCAT within SELECT-CodePudding

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!!!

CodePudding user response：

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