在MySQL中，我可以复制一行插入到同一个表中吗?

insert into table select * from table where primarykey=1

I just want to copy one row to insert into the same table (i.e., I want to duplicate an existing row in the table) but I want to do this without having to list all the columns after the "select", because this table has too many columns.

我只想复制一行插入到同一个表中。，我想在表中复制一个现有的行)但是我想这样做，而不需要在“select”之后列出所有列，因为这个表有太多的列。

But when I do this, I get the error:

但是当我这样做的时候，我得到了一个错误:

Duplicate entry 'xxx' for key 1

1号键重复输入“xxx”

I can handle this by creating another table with the same columns as a temporary container for the record I want to copy:

我可以通过创建另一个与我要复制的记录的临时容器具有相同列的表来处理这个问题:

create table oldtable_temp like oldtable;
insert into oldtable_temp select * from oldtable where key=1;
update oldtable_tem set key=2;
insert into oldtable select * from oldtable where key=2;

Is there a simpler way to solve this?

有更简单的方法来解决这个问题吗?

24 个解决方案

#1

I used Leonard Challis's technique with a few changes:

我使用了Leonard Challis的技术，做了一些改动:

CREATE TEMPORARY TABLE tmptable_1 SELECT * FROM table WHERE primarykey = 1;
UPDATE tmptable_1 SET primarykey = NULL;
INSERT INTO table SELECT * FROM tmptable_1;
DROP TEMPORARY TABLE IF EXISTS tmptable_1;

As a temp table, there should never be more than one record, so you don't have to worry about the primary key. Setting it to null allows MySQL to choose the value itself, so there's no risk of creating a duplicate.

作为临时表，不应该有超过一条记录，所以您不必担心主键。将其设置为null可以让MySQL选择值本身，因此不存在创建副本的风险。

If you want to be super-sure you're only getting one row to insert, you could add LIMIT 1 to the end of the INSERT INTO line.

如果你想要超级确定你只需要插入一行，你可以在插入的末尾加上LIMIT 1。

Note that I also appended the primary key value (1 in this case) to my temporary table name.

注意，我还将主键值(本例中为1)附加到临时表名。

#2

Update 07/07/2014 - The answer based on my answer, by Grim..., is a better solution as it improves on my solution below, so I'd suggest using that.

更新07/07/2014 -答案基于我的回答，由严酷…，是一个更好的解决方案，因为它改进了我下面的解决方案，所以我建议使用它。

You can do this without listing all the columns with the following syntax:

您可以在不列出所有列的情况下使用以下语法:

CREATE TEMPORARY TABLE tmptable SELECT * FROM table WHERE primarykey = 1;
UPDATE tmptable SET primarykey = 2 WHERE primarykey = 1;
INSERT INTO table SELECT * FROM tmptable WHERE primarykey = 2;

You may decide to change the primary key in another way.

您可以决定以另一种方式更改主键。

#3

I'm assuming you want the new record to have a new primarykey? If primarykey is AUTO_INCREMENT then just do this:

我假设你想要新的记录有一个新的主键?如果primarykey是AUTO_INCREMENT那么就可以这样做:

INSERT INTO table (col1, col2, col3, ...)
SELECT col1, col2, col3, ... FROM table
  WHERE primarykey = 1

...where col1, col2, col3, ... is all of the columns in the table except for primarykey.

…其中col1, col2, col3，…除primarykey外，表中的所有列都是。

If it's not an AUTO_INCREMENT column and you want to be able to choose the new value for primarykey it's similar:

如果它不是AUTO_INCREMENT列，并且您想要为primarykey选择新的值，它是相似的:

INSERT INTO table (primarykey, col2, col3, ...)
SELECT 567, col2, col3, ... FROM table
  WHERE primarykey = 1

...where 567 is the new value for primarykey.

…其中567是primarykey的新值。

#4

You could also try dumping the table, finding the insert command and editing it:

您还可以尝试转储该表，找到insert命令并编辑它:

mysqldump -umyuser -p mydatabase --skip-extended-insert mytable > outfile.sql

The --skip-extended-insert gives you one insert command per row. You may then find the row in your favourite text editor, extract the command and alter the primary key to "default".

-skip扩展-insert为每一行提供一个insert命令。然后，您可以在您最喜欢的文本编辑器中找到行，提取命令并将主键更改为“default”。

#5

You almost had it with the your first query you just need to specify the columns, that way you can exclude your primary key in the insert which will enact the auto-increment you likely have on the table to automatically create a new primary key for the entry.

在第一个查询中，您几乎得到了它，您只需要指定列，这样您就可以在insert中排除主键，它将执行表中可能存在的自动增量，以自动为条目创建新的主键。

For example change this:

例如改变这个:

insert into table select * from table where primarykey=1

To this:

:

INSERT INTO table (col1, col2, col3) 
SELECT col1, col2, col3 
FROM table 
WHERE primarykey = 1

Just don't include the primarykey column in either the column list for the INSERT or for the SELECT portions of the query.

只是不要将primarykey列包含在INSERT的列列表中或者查询的SELECT部分中。

#6

This procedure assumes that:

这个过程假定:

• you don't have _duplicate_temp_table
• 你没有_duplicate_temp_table
• your primary key is int
• 您的主键是int
• you have access to create table
• 您可以访问create table

Of course this is not perfect, but in certain (probably most) cases it will work.

当然，这并不完美，但在某些情况下(可能是大多数情况下)，它会起作用。

DELIMITER $$
CREATE PROCEDURE DUPLICATE_ROW(copytable VARCHAR(255), primarykey VARCHAR(255), copyid INT, out newid INT)
BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @error=1;
        SET @temptable = '_duplicate_temp_table';
        SET @sql_text = CONCAT('CREATE TABLE ', @temptable, ' LIKE ', copytable);
        PREPARE stmt FROM @sql_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SET @sql_text = CONCAT('INSERT INTO ', @temptable, ' SELECT * FROM ', copytable, ' where ', primarykey,'=', copyid);
        PREPARE stmt FROM @sql_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SET @sql_text = CONCAT('SELECT max(', primarykey, ')+1 FROM ', copytable, ' INTO @newid');
        PREPARE stmt FROM @sql_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SET @sql_text = CONCAT('UPDATE ', @temptable, ' SET ', primarykey, '=@newid');
        PREPARE stmt FROM @sql_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SET @sql_text = CONCAT('INSERT INTO ', copytable, ' SELECT * FROM ', @temptable, '');
        PREPARE stmt FROM @sql_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SET @sql_text = CONCAT('DROP TABLE ', @temptable);
        PREPARE stmt FROM @sql_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SELECT @newid INTO newid;
END $$
DELIMITER ;

CALL DUPLICATE_ROW('table', 'primarykey', 1, @duplicate_id);
SELECT @duplicate_id;

#7

Some of the following was gleaned off of this site. This is what I did to duplicate a record in a table with any number of fields:

下面的一些内容是从这个网站上收集的。这就是我在表格中复制任意数量字段的记录所做的:

This also assumes you have an AI field at the beginning of the table

这也假定在表的开头有一个AI字段

function duplicateRow( $id = 1 ){
dbLink();//my db connection
$qColumnNames = mysql_query("SHOW COLUMNS FROM table") or die("mysql error");
$numColumns = mysql_num_rows($qColumnNames);

for ($x = 0;$x < $numColumns;$x++){
$colname[] = mysql_fetch_row($qColumnNames);
}

$sql = "SELECT * FROM table WHERE tableId = '$id'";
$row = mysql_fetch_row(mysql_query($sql));
$sql = "INSERT INTO table SET ";
for($i=1;$i<count($colname)-4;$i++){//i set to 1 to preclude the id field
//we set count($colname)-4 to avoid the last 4 fields (good for our implementation)
$sql .= "`".$colname[$i][0]."`  =  '".$row[$i]. "', ";
}
$sql .= " CreateTime = NOW()";// we need the new record to have a new timestamp
mysql_query($sql);
$sql = "SELECT MAX(tableId) FROM table";
$res = mysql_query($sql);
$row = mysql_fetch_row($res);
return $row[0];//gives the new ID from auto incrementing
}

#8

If your table's primary key field is an auto increment field, then you can use query with columns. For example, your table named test_tbl has 3 fields as id, name, age. id is a primary key field and auto increment, so you can use the following query to duplicate the row:

如果表的主键字段是自动递增字段，那么可以使用带有列的查询。例如，名为test_tbl的表有3个字段，分别是id、名称和年龄。id是主键字段和自动增量，因此可以使用以下查询来复制行:

INSERT INTO `test_tbl` (`name`,`age`) SELECT `name`,`age` FROM `test_tbl`;

This query results in duplicating every row.

这个查询导致重复每一行。

---

If your table's primary key field is not an auto increment field, then you can use the following method:

如果表的主键字段不是自动递增字段，则可以使用以下方法:

INSERT INTO `test_tbl` (`id`,`name`,`age`)
  SELECT 20,`name`,`age` FROM `test_tbl` WHERE id = 19;

The result of this query is a duplicate row of id=19 inserted as id=20.

此查询的结果是id=19的重复行插入为id=20。

#9

I might be late in this, but I have a similar solution which has worked for me.

我可能会迟到，但我有一个类似的解决方案。

 INSERT INTO `orders` SELECT MAX(`order_id`)+1,`container_id`, `order_date`, `receive_date`, `timestamp` FROM `orders` WHERE `order_id` = 1

This way I don't need to create a temporary table and etc. As the row is copied in the same table the Max(PK)+1 function can be used easily.

这样我就不需要创建一个临时表等等，因为行被复制到同一个表中，可以很容易地使用Max(PK)+1函数。

I came looking for the solution of this question (had forgotten the syntax) and I ended up making my own query. Funny how things work out some times.

我来寻找这个问题的解决方案(已经忘记了语法)，最后我做了我自己的查询。有趣的是，事情是如何解决的。

Regards

问候

#10

If the Primary Key is Auto Increment, just specify each field except the primary key.

如果主键是自动递增的，只需指定除主键之外的每个字段。

INSERT INTO table(field1,field2,field3) SELECT (field1,field2,field3) FROM table WHERE primarykey=1

插入到表(field1,field2,field3)中选择(field1,field2,field3)，其中primarykey=1

#11

I updated @LeonardChallis's solution as it didn't work for me as none of the others. I removed the WHERE clauses and SET primaryKey = 0 in the temp table so MySQL auto-increments itself the primaryKey

我更新了@LeonardChallis的解决方案，因为它对我来说并不奏效。我删除了WHERE子句，并在temp表中设置primaryKey = 0，这样MySQL就可以自动增加primaryKey了

CREATE TEMPORARY TABLE tmptable SELECT * FROM myTable;
UPDATE tmptable SET primaryKey = 0;
INSERT INTO myTable SELECT * FROM tmptable;

This is of course to duplicate all the rows in the table.

这当然是为了复制表中的所有行。

#12

This can be achieved with some creativity:

这可以通过一些创造性来实现:

SET @sql = CONCAT('INSERT INTO <table> SELECT null, 
    ', (SELECT GROUP_CONCAT(COLUMN_NAME) 
    FROM information_schema.columns 
    WHERE table_schema = '<database>' 
    AND table_name = '<table>' 
    AND column_name NOT IN ('id')), ' 
from <table> WHERE id = <id>');  

PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

This will result in the new row getting an auto incremented id instead of the id from the selected row.

这将导致新行获得自动递增的id，而不是从所选行的id。

#13

I just had to do this and this was my manual solution:

我必须这么做，这是我的手工解决方案:

1. In phpmyadmin, check the row you wish to copy
2. 在phpmyadmin中，检查要复制的行
3. At the bottom under query result operations click 'Export'
4. 在查询结果操作下面，单击“导出”
5. On the next page check 'Save as file' then click 'Go'
6. 在下一页检查'Save as file'然后点击'Go'
7. Open the exported file with a text editor, find the value of the primary field and change it to something unique.
8. 使用文本编辑器打开导出的文件，找到主字段的值并将其更改为唯一的值。
9. Back in phpmyadmin click on the 'Import' tab, locate the file to import .sql file under browse, click 'Go' and the duplicate row should be inserted.
10. 在phpmyadmin中单击“导入”选项卡，找到要在browse下导入.sql文件的文件，单击“Go”，然后插入重复的行。

If you don't know what the PRIMARY field is, look back at your phpmyadmin page, click on the 'Structure' tab and at the bottom of the page under 'Indexes' it will show you which 'Field' has a 'Keyname' value 'PRIMARY'.

如果您不知道主字段是什么，请查看phpmyadmin页面，单击“结构”选项卡，在“索引”下的页面底部，它将显示哪些“字段”有“Keyname”值“PRIMARY”。

Kind of a long way around, but if you don't want to deal with markup and just need to duplicate a single row there you go.

有点远，但是如果你不想处理标记，只需要复制一行就可以了。

#14

I used Grim's technique with a little change: If someone looking for this query is because can't do a simple query due to primary key problem:

我使用了格里姆的技术，并做了一点改动:如果有人查找这个查询，是因为由于主键问题不能执行简单的查询:

INSERT INTO table SELECT * FROM table WHERE primakey=1;

With my MySql install 5.6.26, key isn't nullable and produce an error:

使用我的MySql安装5.6.26，密钥不可为空并产生错误:

#1048 - Column 'primakey' cannot be null 

So after create temporary table I change the primary key to a be nullable.

因此，在创建临时表之后，我将主键改为a为nullable。

CREATE TEMPORARY TABLE tmptable_1 SELECT * FROM table WHERE primarykey = 1;
ALTER TABLE tmptable_1 MODIFY primarykey int(12) null;
UPDATE tmptable_1 SET primarykey = NULL;
INSERT INTO table SELECT * FROM tmptable_1;
DROP TEMPORARY TABLE IF EXISTS tmptable_1;

#15

I would use below,

我会用下面,

insert into ORDER_ITEM select * from ORDER_ITEM where ITEM_NUMBER =123;

#16

Sorry for the necropost but this is what I turned up with google and since I found this helpful but problematic I wanted to contribute an important modification for anyone else who digs this up.

很抱歉我找到了谷歌因为我发现了这个有用但有问题的东西，所以我想为任何想要了解这个的人做一个重要的修改。

First off, I'm using SQL Server, not MySQL, but I think it should work similarly. I used Leonard Challis' solution because it was simplest and met the need, however there's a problem with this - if you simply take the PK and increment it by 1 then what happens if you've added other records since the row in question was added. I decided it was best to just let the system handle the autoincrementing of the PK, so I did the following:

首先，我使用的是SQL Server，而不是MySQL，但我认为它应该类似地工作。我使用了Leonard Challis的解决方案，因为它是最简单的，并且满足了我们的需要，但是有一个问题——如果你只取PK，然后对它加1，那么如果你添加了其他的记录，因为添加了相关的行之后会发生什么呢?我认为最好是让系统处理PK的自动递增，所以我做了如下工作:

SELECT * INTO #tmpTable FROM Table WHERE primarykey = 1
--Optionally you can modify one or more fields here like this: 
--UPDATE #tmpTable SET somefield = newData
ALTER TABLE #tmpTable DROP COLUMN TicketUpdateID
INSERT INTO Tickets SELECT * FROM #tmpTable
DROP TABLE #tmpTable

I believe this would work similarly in MySQL, but I can't test this, sorry

我相信这个在MySQL中也可以用，但是我不能测试这个，抱歉

#17

I know it's an old question, but here is another solution:

我知道这是个老问题，但这里有另一个解决办法:

This duplicates a row in the main table, assuming the primary key is auto-increment, and creates copies of linked-tables data with the new main table id.

这复制了主表中的一行，假设主键是自动递增的，并使用新的主表id创建linked表数据的副本。

Other options for getting column names:
-SHOW COLUMNS FROM tablename; (Column name: Field)
-DESCRIBE tablename (Column name: Field)
-SELECT column_name FROM information_schema.columns WHERE table_name = 'tablename' (Column name: column_name)

获取列名的其他选项:-显示来自tablename的列;(列名:字段)-描述tablename(列名:字段)-从information_schema中选择column_name。表_name = 'tablename'的列(列名:column_name)

//First, copy main_table row
$ColumnHdr='';
$Query="SHOW COLUMNS FROM `main_table`;";
$Result=Wrappedmysql_query($Query,$link,__FILE__,__LINE__);
while($Row=mysql_fetch_array($Result))
{
    if($Row['Field']=='MainTableID')     //skip main table id in column list
        continue;
    $ColumnHdr.=",`" . $Row['Field'] . "`";
}
$Query="INSERT INTO `main_table` (" . substr($ColumnHdr,1) . ")
        (SELECT " . substr($ColumnHdr,1) . " FROM `main_table`
            WHERE `MainTableID`=" . $OldMainTableID . ");";
$Result=Wrappedmysql_query($Query,$link,__FILE__,__LINE__);
$NewMainTableID=mysql_insert_id($link);

//Change the name (assumes a 30 char field)
$Query="UPDATE `main_table` SET `Title`=CONCAT(SUBSTRING(`Title`,1,25),' Copy') WHERE `MainTableID`=" . $NewMainTableID . ";";
$Result=Wrappedmysql_query($Query,$link,__FILE__,__LINE__);

//now copy in the linked tables
$TableArr=array("main_table_link1","main_table_link2","main_table_link3");
foreach($TableArr as $TableArrK=>$TableArrV)
{
    $ColumnHdr='';
    $Query="SHOW COLUMNS FROM `" . $TableArrV . "`;";
    $Result=Wrappedmysql_query($Query,$link,__FILE__,__LINE__);
    while($Row=mysql_fetch_array($Result))
    {
        if($Row['Field']=='MainTableID')     //skip main table id in column list, re-added in query
            continue;
        if($Row['Field']=='dbID')    //skip auto-increment,primary key in linked table
            continue;
        $ColumnHdr.=",`" . $Row['Field'] . "`";
    }

    $Query="INSERT INTO `" . $TableArrV . "` (`MainTableID`," . substr($ColumnHdr,1) . ")
            (SELECT " . $NewMainTableID . "," . substr($ColumnHdr,1) . " FROM `" . $TableArrV . "`
             WHERE `MainTableID`=" . $OldMainTableID . ");";
    $Result=Wrappedmysql_query($Query,$link,__FILE__,__LINE__);
}

#18

max233 was certainly on the right track, at least for the autoincrement case. However, do not do the ALTER TABLE. Simply set the auto-increment field in the temporary table to NULL. This will present an error, but the following INSERT of all fields in the temporary table will happen and the the NULL auto field will obtain a unique value.

max233肯定是正确的，至少对于自动递增的情况是这样的。但是，不要做更改表。简单地将临时表中的自动增量字段设置为NULL。这将显示一个错误，但是在临时表中插入所有字段将发生，NULL auto字段将获得一个惟一值。

#19

Create a table

创建一个表

    CREATE TABLE `sample_table` (
       `sample_id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
       `sample_name` VARCHAR(255) NOT NULL,
       `sample_col_1` TINYINT(1) NOT NULL,
       `sample_col_2` TINYINT(2) NOT NULL,

      PRIMARY KEY (`sample_id`),
      UNIQUE KEY `sample_id` (`sample_id`)

    ) ENGINE='InnoDB' DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

Insert a row

插入一行

INSERT INTO `sample_table`
   VALUES(NULL, 'sample name', 1, 2);

Clone row insert above

克隆上面一行插入

INSERT INTO `sample_table`
   SELECT 
    NULL AS `sample_id`, -- new AUTO_INCREMENT PRIMARY KEY from MySQL
    'new dummy entry' AS `sample_name`,  -- new UNIQUE KEY from you
    `sample_col_1`, -- col from old row
    `sample_col_2` -- col from old row
   FROM `sample_table`
   WHERE `sample_id` = 1;

Test

测试

SELECT * FROM `sample_table`;

#20

Here's an answer I found online at this site Describes how to do the above1 You can find the answer at the bottom of the page. Basically, what you do is copy the row to be copied to a temporary table held in memory. You then change the Primary Key number using update. You then re-insert it into the target table. You then drop the table.

这是我在这个网站上找到的答案，描述了如何做以上的事情。基本上，您所做的是将该行复制到内存中保存的临时表中。然后使用update更改主键号。然后将其重新插入到目标表中。然后放下桌子。

This is the code for it:

这是它的代码:

CREATE TEMPORARY TABLE rescueteam ENGINE=MEMORY SELECT * FROMfitnessreport4 WHERE rID=1;# 1 row affected. UPDATE rescueteam SET rID=Null WHERE rID=1;# 1 row affected.INSERT INTO fitnessreport4 SELECT * FROM rescueteam;# 1 row affected. DROP TABLE rescueteam# MySQL returned an empty result set (i.e. zero
rows).

创建临时表save eteam ENGINE=MEMORY SELECT * FROMfitnessreport4，其中rID=1;# 1行受影响。更新rescueteam SET rID=Null，其中rID=1;# 1行受影响。插入到fitnessreport4中选择* FROM rescue eteam;第一行受影响。删除表救援团队# MySQL返回一个空结果集(即零行)。

I created the temporary table rescueteam. I copied the row from my original table fitnessreport4. I then set the primary key for the row in the temporary table to null so that I can copy it back to the original table without getting a Duplicate Key error. I tried this code yesterday evening and it worked.

我创建了临时表救援团队。我从我的原始表格fitnessreport4中复制了这一行。然后，我将临时表中的行的主键设置为null，以便可以将其复制回原始表，而不会得到重复的键错误。我昨天晚上尝试了这个代码，它成功了。

#21

For a very simple solution, you could use PHPMyAdmin to export the row as a CSV file then simply import the amended CSV file. Editing the ID/primarykey column to show no value before you import it.

对于一个非常简单的解决方案，您可以使用PHPMyAdmin将行导出为CSV文件，然后简单地导入修改后的CSV文件。编辑ID/primarykey列，在导入它之前显示没有任何值。

SELECT * FROM table where primarykey=1

Then at the bottom of the page:

然后在这一页的底部:

Where is says "Export" simply export, then edit the csv file to remove the primarykey value, so it's empty, and then just import it into the database, a new primarykey will be assigned on import.

其中is表示“导出”，简单地导出，然后编辑csv文件以删除primarykey值，所以它是空的，然后将它导入数据库，新的primarykey将在导入时被分配。

#22

clone row with update fields and auto increment value

使用更新字段和自动递增值克隆行

CREATE TEMPORARY TABLE `temp` SELECT * FROM `testing` WHERE id = 14;

UPDATE `temp` SET id = (SELECT id FROM testing ORDER by id DESC LIMIT 1
 )+1, user_id = 252 ,policy_no = "mysdddd12" where id = 14;

INSERT INTO `testing` SELECT * FROM `temp`;

DROP TEMPORARY TABLE IF EXISTS `temp`;

#23

Just wanted to post my piece of PHP code, because I think the way I collect the columns is a bit cleaner in code than the previous examples. Also this shows how you could easily alter an field, in this case adding a string. But you could also replace a foreign key field with the newly added record, in case you want to copy some child records as well.

我只想发布我的PHP代码，因为我认为我收集这些列的方式比以前的示例代码更简洁。这也显示了如何轻松地修改字段，在本例中添加一个字符串。但是，您也可以用新添加的记录替换一个外键字段，以备您也需要复制一些子记录。

  // Read columns, unset the PK (always the first field in my case)
  $stmt = $conn->prepare('SHOW COLUMNS FROM template');
  $stmt->execute();

  $columns = $stmt->fetchAll();
  $columns = array_map(function ($element) { return $element['Field']; }, $columns);

  unset($columns[0]);

  // Insert record in the database. Add string COPY to the name field.
  $sql = "INSERT INTO `template` (".implode(",", $columns).")";
  if ($key = array_search('name', $columns))
      $columns[$key] = "CONCAT(name, ' COPY')";
  $sql .= " SELECT ".implode(",", $columns)." FROM `template` WHERE `id` = ".$id;

  $stmt = $conn->prepare($sql);
  $stmt->execute();

#24

This solution showed above works perfect also for selected rows. For example I am creating demonstration rows for my nice2work project, and this works perfect.

上面显示的这个解决方案对于选定的行也非常适用。例如，我正在为我的nice2work项目创建演示行，这个工作非常完美。

CREATE TEMPORARY TABLE tmptable SELECT * FROM myTable WHERE id=500;
UPDATE tmptable SET id = 0;
UPDATE some fields I need to change
INSERT INTO myTable SELECT * FROM tmptable;
DROP TABLE tmptable;

//  You can use this same also directly into your code like (PHP Style)
$sql = "CREATE TEMPORARY TABLE tmptable SELECT * FROM myTable WHERE id=500;
UPDATE tmptable SET id = 0;
UPDATE some fields I need to change
INSERT INTO myTable SELECT * FROM tmptable;DROP TABLE tmptable;";