使用更改和自动递增ID重复数据库中的多行

问题描述：

In short what I need to do is:

Duplicate X amount of rows in a table
Alter a few fields
Keep the auto incrementing IDs in order.

This is what I have going so far.

$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");

When I run it I get up to this line

$this->db->query("INSERT INTO orders_products SELECT * FROM tmpTbl");

and it throws me this error

Duplicate entry '54' for key 'PRIMARY

INSERT INTO orders_products SELECT * FROM tmpTbl

The reason I created the counter in the first place was to be a fix for this problem. However, I am sadly mistaken.

简而言之，我需要做的是： p>

表格中的X行数量重复 li>
更改几个字段 li>

按顺序保留自动递增ID。 li> ol> \ n

这是我到目前为止所做的。 p>

  $ this-＆gt; db-＆gt; query（“CREATE TEMPORARY TABLE tmpTbl AS SELECT * FROM orders_products  WHERE op_order_id = $ id“）; 
 $ temp = $ this-＆gt; getAll（'tmpTbl'）; 
 
 ##抓住orders_products表中的最高ID，这样我们就可以在
 $ this之后开始auto_inc了  - ＆gt; db-＆gt; select_max（“op_id”）; 
 $ max = $ this-＆gt; getAll（'orders_products'）; 
 ##将id设置为循环的计数器
 $ counter = $  max [0]  - ＆gt; op_id; 
 
 ##根据需要循环遍历结果编辑字段
foreach（$ temp as $ t）：
 $ counter ++; 
 $ this-＆gt; db-＆gt; query  （“UPDATE tmpTbl SET op_id = $ counter，op_order_id = $ orderId WHERE op_order_id = $ id”）; 
endforeach; 
 $ temp = $ this-＆gt; getAll（'tmpTbl'）; 
 
 ## insert the 新的重复进入orders_produ  cts table 
 $ this-＆gt; db-＆gt; query（“INSERT INTO orders_products SELECT * FROM tmpTbl”）; 
 
 ##删除临时表，使其在下次复制时清新干净
 $ this  - ＆gt; db-＆gt; query（“DROP TEMPORARY TABLE tmpTbl”）;  
  code>  pre> 
 
 当我运行它时，我走到这一行 p> 
 
 
  $ this-＆gt; db-＆gt; 查询（“INSERT INTO orders_products SELECT * FROM tmpTbl”）; 
  code>  pre> 
 
 它会抛出此错误 p> 
 
 
  键'PRIMARY 
 
INSERT INTO orders_products SELECT * FROM tmpTbl 
  code>  pre> 
 
 重复条目'54'我首先创建计数器的原因是 解决这个问题。 但是，我很遗憾地错了。 p> 
  div>

答

What I would do In your case is to remove the ID col. from the SELECT so that the INSERT will generate automatically the ID.

INSERT INTO orders_products (column2,column3,etc)
    SELECT column2, column3, etc FROM tmpTbl

使用更改和自动递增ID重复数据库中的多行

相关推荐