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

After

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

networknodes 3 areas have network_node_id, Network_id, and node_id are Fanian key references to the previous two networks, and a network can contain several copies of the same node field networkconnection_id , start_network_node_id, end_network_node_id

now I want (but with different network_node_id)

networkconnections create a network entry duplicates, including a copy of the record in network connections and network connections, a new network Table records become The money is included.

How can this be done with MySQL stored procedures? Is it possible to use arrays and loop in a single query?

  drop process if 'duplicate network'; Create process 'duplicate network' (Network_key four (50)) Disclaimer Newnetink Binier (16); // Use a unique function Generate NewKey () newNetworkId; // Network (NETWORK_ID, labels, AppBackgroundColorKey, DateAdded, LastModified) Select newNetworkId, labels, AppBackgroundColorKey, DateAdded, NOW () Create insert a new network, where NETWORK_ID = network_key network; // // Copy the networknodes reords network_key Neo, an array or ID of some other structure NN INSERT INTO networknodes (network_node_id, NETWORK_ID, node_id, DateAdded, LastModified) Select NewKey () is called recored // requirement made dtore for Stored curresponding this value NN newNetworkId, node_id, added date, now () NetworkNET from WHERE network_id = network_key; // copy networkconnections reords network_key is // wrong part curresponding, I do not know how to let it help here need select Insert networkconnections (networkconnection_id, start_network_node_id, end_network_node_id, DateAdded) NewKey (), (select NWN_start Network_node_id ...), (SELECT NWN_end.network_node_id ...), Network Connection from Network Connection at INNER NetworkNET as NWN_start. Start_network_node_id = NWN_start.network_node_id Netvrkknekshn as Andblyuen_end as INNER network. End_network_node_id = connect NWN_END.network_node_id / / or NWN_start.networl_id = Network_key; // For the network connection of each room, replace the old NetworkNetDID with both the new NetworkNet and replace the end nodes END;  

It would be great to alter any algorithm /

Yes, create a temporary table to catch those values, then you can reference the old values. Keep the first query, then:

Temporary table TMPNetworkNode (... schema here ... ...)

  Enter INMPER NewKey () to NewNetworkKey Choose the form, // nn newNetworkId, newNodeId () in this value dtore as newNodeId oldNodeId, NetworkNodes from dateAdded form node_id where NETWORK_ID = network_key; Insert in NetworkNodes (Network_Node_ID, Network_ID, Node_ID, DATEDED, LTRODIFIED), select NewNetworkKey, newNetworkId, newNodeId, DateAdded, now connect to Network Connection (Network Connection_ID, start_network_node_id, end_network_node_id, Date added) in tmpNetworkNodes NewKey ( ), NWN_start.newNodeId, select NWN_end .newNodeId, Netvrkknekshn on NWN_start as INNER JOIN tmpNetworkNodes network connection. start_network_node_id = NWN_start.oldNodeId INNER NWN_END network as a joint Timpinetvrkanod On Nekshn. Nad_network_ode_id = NWN_END.oldNodeId  

DROP temporary table TMPNetworkNodes

Because the temporary table contains only elements for the network that is interested in you, the internal query that is included in the last query Filter anything from other networks.

I think that you have a function or newNodeId like you do for NetworkKey (), so there is no identity / autoincrement in the column, because I can also misunderstand your schema, because It is difficult to follow relationships completely without glasses.

Hope it helps!