MySQL stored procedure for duplicating records from 4 related tables

Question

I have 3 tables, networks, nodes, network nodes, network connections.

networknodes has 3 fields network_node_id, network_id and node_id, the last two are the key forien links for the network. and the network may include several copies of the same node (but with a different network_node_id file)

network connections have the files networkconnection_id, start_network_node_id, end_network_node_id

Now I want to duplicate the record in the network, this includes creating a new record of the network table, creating copies of the records in network nodes and network connections.

How can this be done with MySQL stored procedures? Is it possible to do this in a single request without using arrays and for a loop?

DROP PROCEDURE IF EXISTS `DuplicateNetwork`;

CREATE PROCEDURE `DuplicateNetwork`(network_key char(50))
BEGIN
    DECLARE newNetworkId BINARY(16);

    // Generate a Unique using function
    SELECT NewKey() INTO newNetworkId;
    // Create a new Network
    INSERT INTO networks (network_id,   
       Label,
       AppBackgroundColorKey,
       DateAdded,LastModified)
        SELECT newNetworkId,
             Label,AppBackgroundColorKey,
             DateAdded,
             NOW(),
        FROM networks 
        WHERE network_id = network_key; 
    // Copy networknodes reords curresponding to network_key    
    // Store the ids of newly created recored to an array or some other structure called NN 
    INSERT INTO networknodes (
                network_node_id,
                network_id,
                node_id,
                DateAdded,
                LastModified)
            SELECT NewKey(), // Need to dtore this value in NN
                    newNetworkId,
                    node_id,
                    DateAdded,
                    NOW() 
            FROM networknodes 
        WHERE network_id = network_key; 

    // Copy networkconnections reords curresponding to network_key  
    // This part is incorrect, i don't know how two make it, help is needed here    
    INSERT INTO networkconnections(networkconnection_id,
            start_network_node_id,
            end_network_node_id,
            DateAdded) 
            SELECT NewKey(),
             (SELECT  NWN_start.network_node_id ...),
             (SELECT  NWN_end.network_node_id ...),
             FROM networkconnection 
             INNER JOIN networknodes AS NWN_start ON networkconnection.start_network_node_id=NWN_start.network_node_id
             INNER JOIN networknodes AS NWN_end   ON networkconnection.end_network_node_id  =NWN_end.network_node_id 
             //WHERE NWN_start.networl_id = network_key;
    // For each room network connection
        replace the old networknode_id with new networknode_id for both start and end nodes
END;

/

Answer 1

, , , . , :

tmpNetworkNodes (... ... )

INSERT INTO tmpNetworkNodes
        SELECT NewKey() as newNetworkKey, // Need to dtore this value in NN
                newNetworkId,
                newNodeId() as newNodeId
                node_id as oldNodeId,
                DateAdded
        FROM Networknodes 
        WHERE network_id = network_key;   

INSERT INTO networknodes (
            network_node_id,
            network_id,
            node_id,
            DateAdded,
            LastModified)
SELECT newNetworkKey, newNetworkId, newNodeId, DateAdded, Now() 
       from tmpNetworkNodes

INSERT INTO networkconnections(
        networkconnection_id,
        start_network_node_id,
        end_network_node_id,
        DateAdded) 
 SELECT NewKey(),
        NWN_start.newNodeId,
        NWN_end.newNodeId,
  FROM networkconnection 
       INNER JOIN tmpNetworkNodes AS NWN_start 
       ON networkconnection.start_network_node_id=NWN_start.oldNodeId
       INNER JOIN tmpNetworknodes AS NWN_end   
       ON networkconnection.end_network_node_id  =NWN_end.oldNodeId 

DROP tmpNetworkNodes

, - .

, newNodeId, networkKey(), /. , , .

, !