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

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;)