StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL stored procedure for duplicating records from 4 inter-related tables
text
Body
copied!<p>I have 3 tables, networks, nodes, networknodes, networkconnections.</p> <p>networknodes has 3 fields network_node_id, network_id, and node_id, the last two are forien key references to network. and <strong>a network may include multiple copies of same node</strong> (but with diffrent network_node_id)</p> <p>networkconnections has the fileds networkconnection_id, start_network_node_id, end_network_node_id</p> <p>Now i want to duplicate a network entry, this includes creating a new network table record, creating copies of records in networknodes and networkconnections.</p> <p>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?</p> <pre><code>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; </code></pre> <p>Would be greatful Any body can improve/complete the Algorithm</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload