MySQL stored procedure for duplicating records from 4 inter-related tables

Question

I have 3 tables, networks, nodes, networknodes, networkconnections.

networknodes has 3 fields network_node_id, network_id, and node_id, the last two are forien key references to network. and a network may include multiple copies of same node (but with diffrent network_node_id)

networkconnections has the fileds networkconnection_id, start_network_node_id, end_network_node_id

Now i want to duplicate a network entry, this includes creating a new network table record, creating copies of records in networknodes and networkconnections.

How can be done this with MySQL stored procedures? Is it possible to do this in a single query with out using arrays and for 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;

Would be greatful Any body can improve/complete the Algorithm

Answer 1

Yes, create a temporary table to hold the values you are copying, then you can keep a reference to the old values. Keep the first query, then do:

CREATE TEMPORARY TABLE tmpNetworkNodes( ... schema in here ... )

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 TEMPORARY TABLE tmpNetworkNodes

Because the temporary table only contains the elements for the network you are interested in, the inner joins in the last query will filter out anything from the other networks.

I assume you have a function for newNodeId like you do for networkKey(), so none of the columns are identity/autoincrement. I may also have your schema wrong, as it's hard to follow what the relationships are without the full table specs.

Hope it helps!