Mysql Stored Procedure to copy records in a Parent/Child/Grandchild hierarchy

Question

I have 3 tables parent, child & grandchild of the form:

+----------------+   +----------------+   +---------------------+
| parent         |   | child          |   | grandchild          |
+----------------+   +----------------+   +---------------------+
| parent_id (PK) |   | child_id (PK)  |   | grandchild_id (PK)  |
| parent_data    |   | child_data     |   | grandchild_data     |
+----------------+   | parent_id (FK) |   | child_id (FK)       |
                     +----------------+   +---------------------+ 

PK = auto-incrementing primary key.
FK = foreign key.

I want a stored procedure that can copy a record in the parent table and any associated records in the child and grandchild tables. I can get the parent and child data copied ok, its the grandchild table I'm struggling with. This is as far as I've got:

CREATE FUNCTION sf_copy_parent(p_parent_id INT) RETURNS INT
BEGIN
    DECLARE new_parent_id INT;

    -- create new parent record
    INSERT INTO parent(parent_data)
        SELECT parent_data FROM parent
        WHERE parent_id=p_parent_id;
    SET new_parent_id=LAST_INSERT_ID();

    -- copy child records
    INSERT INTO child(child_data,parent_id)
        SELECT child_data,new_parent_id FROM child
        WHERE parent_id=p_parent_id;

    -- copy grandchild records ???


    -- return
    RETURN new_parent_id;
END

I'm using Mysql5.5 if that's important.

Answer 1

EDIT: I have since created another answer that I believe is simpler and better than this one.

The completed stored procedure then using answer from @Devart is:

CREATE FUNCTION `sp_copy`(p_parent_id INT) RETURNS int(11)
BEGIN
    DECLARE new_parent_id INT;

    -- create new parent record
    INSERT INTO parent(parent_data)
        SELECT parent_data FROM parent
        WHERE parent_id=p_parent_id;
    SET new_parent_id=LAST_INSERT_ID();

    -- copy child records
    INSERT INTO child(child_data,parent_id)
        SELECT child_data,new_parent_id FROM child
        WHERE parent_id=p_parent_id;

    -- copy grandchild records
    SET @r1 = 1;
    SET @child_id = NULL;
    SET @r2 = 0;
    INSERT INTO grandchild(grandchild_data,child_id) SELECT c1.grandchild_data, c2.child_id FROM (
    SELECT @r1 := if(c.child_id IS NULL OR c.child_id <> @child_id, @r1 + 1, @r1) rank, @child_id := c.child_id, c.child_id, g.grandchild_data FROM child c
    JOIN grandchild g
        ON c.child_id = g.child_id
    WHERE
        c.parent_id = p_parent_id
    ORDER BY
        c.child_id, g.grandchild_id
    ) c1
    JOIN (SELECT @r2 := @r2 + 1 rank, child_id FROM child WHERE parent_id = new_parent_id ORDER BY child_id) c2
        ON c1.rank = c2.rank;

    -- return new parent id
    RETURN new_parent_id;
END

Answer 2

I've since learnt a bit more and I believe the contribution from @Devart can be simplified a little. The solution I now use is shown below.

NOTE: This solution only works correctly if the tables used are InnoDB and not MyISAM. I think this has something to do with how the default sort behaviour of MyISAM works. (InnoDB uses primary key whereas MyISAM uses insertion order.) In particular, with MyISAM tables, the two sets of ranks generated in the 'copy grandchild(s)' section are not 'in-sync' with each other. Adding SORT BY clauses in the relevant parts doesn't seem to make a difference either.

CREATE PROCEDURE sp_copy(p_parent_id INT)
BEGIN
    DECLARE new_parent_id INT;

    -- copy parent
    INSERT INTO parent(parent_data) SELECT parent_data FROM parent WHERE parent_id=p_parent_id;
    SET new_parent_id:=LAST_INSERT_ID();

    -- copy child(s)
    INSERT INTO child(child_data, parent_id)
        SELECT child_data, new_parent_id FROM child WHERE parent_id=p_parent_id;

    -- copy grandchild(s)
    SET @rank1:=0;        
    SET @rank2:=0;            
    INSERT INTO grandchild(grandchild_data, child_id) SELECT gc.grandchild_data, c2.child_id FROM
        (SELECT child_id, @rank1:=@rank1+1 as rank FROM child WHERE parent_id=p_parent_id) c1
        INNER JOIN
        (SELECT child_id, @rank2:=@rank2+1 as rank FROM child WHERE parent_id=new_parent_id) c2 ON c1.rank=c2.rank
        INNER JOIN grandchild gc ON c1.child_id=gc.child_id;

END

Also, to handle a greatgrandchild table the same principle in copying the grandchild records can be used. The only additional complexity is to add a join in each of the two subqueries. This is required because the child.parent_id field needs referencing in the WHERE clause:

-- copy greatgrandchild(s)
SET @rank1:=0;        
SET @rank2:=0;            
INSERT INTO greatgrandchild(greatgrandchild_data, grandchild_id) SELECT ggc.greatgrandchild_data, gc2.grandchild_id FROM
    (SELECT grandchild_id, @rank1:=@rank1+1 as rank FROM grandchild INNER JOIN child ON child.child_id=grandchild.child_id WHERE parent_id=p_parent_id) gc1
    INNER JOIN
    (SELECT grandchild_id, @rank2:=@rank2+1 as rank FROM grandchild INNER JOIN child ON child.child_id=grandchild.child_id WHERE parent_id=new_parent_id) gc2 ON gc1.rank=gc2.rank
    INNER JOIN greatgrandchild ggc ON gc1.grandchild_id=ggc.grandchild_id;

Answer 3

Try this SELECT query (it uses 'p_parent_id' and 'new_parent_id' variables) -

SET @r1 = 1;
SET @child_id = NULL;
SET @r2 = 0;

SELECT c1.grandchild_data, c2.child_id FROM (
  SELECT @r1 := if(c.child_id IS NULL OR c.child_id <> @child_id, @r1 + 1, @r1) rank, @child_id := c.child_id, c.child_id, g.grandchild_data FROM child c
  JOIN grandchild g
    ON c.child_id = g.child_id
  WHERE
    c.parent_id = p_parent_id
  ORDER BY
    c.child_id, g.grandchild_id
  ) c1
JOIN (SELECT @r2 := @r2 + 1 rank, child_id FROM child WHERE parent_id = new_parent_id ORDER BY child_id) c2
  ON c1.rank = c2.rank;

If it works, we will rewrite it to INSERT..SELECT statement, or try to do it yourself;)