Geek Answers Handbook

Mysql Stored procedure for copying records in parent / child / grandson hierarchy

I have 3 form parent, child and grandchild tables:

+----------------+   +----------------+   +---------------------+
| 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 = primary key with automatic addition.
FK = foreign key.

I want the stored procedure to be able to copy the record into the parent table and any related records in the tables for children and grandchildren. I can get the parent and child data copied in order, this is the grandchildren table I am struggling with. This is as far as I know:

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 use Mysql5.5 if it is important.

+5
source share
3 answers

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

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;

, INSERT..SELECT ;)

+4

EDIT. , , , , .

@Devart:

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
0

, , @Devart . , , .

. , InnoDB, MyISAM. , - , MyISAM. (InnoDB , MyISAM ). , MyISAM , " ", "" . SORT BY , , .

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

In addition, you can use the same principle when copying greatgrandchild tables when copying grandson records. The only additional difficulty is adding a join to each of the two subqueries. This is necessary because the child.parent_id field requires a link 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;
0
source

More articles:


All Articles