StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODuplicating multiple rows in a database with changes and auto incrementing Ids
text
Body
copied!<p>In short what I need to do is:</p> <ol> <li>Duplicate X amount of rows in a table</li> <li>Alter a few fields</li> <li>Keep the auto incrementing IDs in order.</li> </ol> <p>This is what I have going so far.</p> <pre><code>$this->db->query("CREATE TEMPORARY TABLE tmpTbl AS SELECT * FROM orders_products WHERE op_order_id = $id"); $temp = $this->getAll('tmpTbl'); ## grab the highest id in the orders_products table so we can begin auto_inc after that $this->db->select_max("op_id"); $max = $this->getAll('orders_products'); ## set the id to a counter for the loop $counter = $max[0]->op_id; ## loop through the results editing fields as needed foreach ( $temp as $t ) : $counter++; $this->db->query("UPDATE tmpTbl SET op_id = $counter, op_order_id = $orderId WHERE op_order_id = $id"); endforeach; $temp = $this->getAll('tmpTbl'); ## insert the new duplications into the orders_products table $this->db->query("INSERT INTO orders_products SELECT * FROM tmpTbl"); ## drop the temp table so it is fresh and clean for the next duplication $this->db->query("DROP TEMPORARY TABLE tmpTbl"); </code></pre> <p>When I run it I get up to this line</p> <pre><code>$this->db->query("INSERT INTO orders_products SELECT * FROM tmpTbl"); </code></pre> <p>and it throws me this error</p> <pre><code>Duplicate entry '54' for key 'PRIMARY INSERT INTO orders_products SELECT * FROM tmpTbl </code></pre> <p>The reason I created the counter in the first place was to be a fix for this problem. However, I am sadly mistaken.</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