复制行但使用新的ID [英] Copy row but with new id

问题描述

我有一个表"test"，它具有自动递增的id和任意数量的列.

I have a table "test" with an auto incremented id and an arbitrary number of columns.

我想在此表中复制一行，所有列都相同，除了id.

I want to make a copy of a row in this table with all columns the same except for the id of course.

是否可以在不命名所有列的情况下执行此操作?

Is there a way to do this without naming all columns?

我以为INSERT... SELECT... ON DUPLICATE KEY会对我有所帮助，直到我意识到它永远不会成为INSERT ON DUPLICATE为止，它只会更新现有行.

I thought INSERT... SELECT... ON DUPLICATE KEY would help me until I realised that it never makes an INSERT ON DUPLICATE, it just updates the existing row.

推荐答案

让我们说您的表具有以下字段:

Let us say your table has following fields:

( pk_id int not null auto_increment primary key,
  col1 int,
  col2 varchar(10)
)

然后，将具有新键值的值从一行复制到另一行， 以下查询可能会帮助

then, to copy values from one row to the other row with new key value, following query may help

insert into my_table( col1, col2 ) select col1, col2 from my_table where pk_id=?;

这将为pk_id字段生成一个新值，并从所选行的col1和col2中复制值.

This will generate a new value for pk_id field and copy values from col1, and col2 of the selected row.

您可以扩展此示例以在表格中申请更多字段.

You can extend this sample to apply for more fields in the table.

更新:
在适当方面尊重JohnP和Martin的评论-

我们可以使用临时表首先从主表中缓冲，然后使用它再次复制到主表中. 仅更新临时表中的pk参考字段将无济于事，因为它可能已存在于主表中.相反，我们可以从临时表中删除pk字段，并将所有其他字段复制到主表中.

We can use temporary table to buffer first from main table and use it to copy to main table again. Mere update of pk reference field in temp table will not help as it might already be present in the main table. Instead we can drop the pk field from the temp table and copy all other to the main table.

参考希望这会有所帮助.

这篇关于复制行但使用新的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！