Duplicate a record in MySQL

Question

I have a table and I want to duplicate specific rows in the table. I know this is not the best way to do things but we are looking for a quick solution.

Here's something harder than I initially thought, all I need to do is copy an entire record to a new record in an auto-increment table in MySql without the need to specify each field. This is because the table can change in future and might break duplication. I will be duplicating MySQL records from PHP.

It is a problem because in a 'SELECT * ' query MySql will try to copy the ID of the record being copied which genenerates a duplicate ID error.

This blocks out: INSERT INTO customer SELECT * FROM customer WHERE customerid=9181. It also blocks out INSERT INTO customer (Field1, Field2, ...) SELECT Field1, Field2, ..... FROM customer WHERE customerid=9181.

Is there a way to do this from PHP or MySQL?

Answer 1

How about copying the the rows into a temp table and then back into the customer table? I think that would force mysql to give it a new autoincrement ID, especially with the second version of the query you posted.

Answer 2

What about

insert into test.abc select null, val1, val2 from test.abc where val2 = some_condition;

Seems to work for me like that. Substitute your table, fields, condition of course.

The null lets the DB generate the auto-increment ID for you.

Answer 3

Two posts so far, and here is a 3rd option: In PHP, dynamically determine the fields in your table. Or, you can make an array list of fields that you "hard-code", maintain by hand to reflect the table. Your resulting query won't use *.

Answer 4

Options:

1. Avoid using the * wildcard, and specify all the columns yourself. But you say the table is expected to change in the future and this isn't a workable solution for your case.

2. Introspect the columns from the current table definition (using DESCRIBE or by querying INFORMATION_SCHEMA.COLUMNS). Use this information about metadata to build the query, omitting the auto-increment primary key column.

3. Do the SELECT * query, fetch the row back into your application, and then remove the auto-increment column from it. Then use that tuple to construct an INSERT statement.

In short, you have a complex requirement to adapt to changing metadata. That probably can't be done in a single query.

Answer 5

have you considered using a trigger instead to handle the duplication automatically?

Answer 6

I finally found this code. I am sure it will help people in the future. So here it is.

function DuplicateMySQLRecord ($table, $id_field, $id) {
  // load the original record into an array
  $result = mysql_query("SELECT * FROM {$table} WHERE {$id_field}={$id}");
  $original_record = mysql_fetch_assoc($result);

  // insert the new record and get the new auto_increment id
  mysql_query("INSERT INTO {$table} (`{$id_field}`) VALUES (NULL)");
  $newid = mysql_insert_id();

  // generate the query to update the new record with the previous values
  $query = "UPDATE {$table} SET ";
  foreach ($original_record as $key => $value) {
    if ($key != $id_field) {
        $query .= '`'.$key.'` = "'.str_replace('"','\"',$value).'", ';
    }
  }
  $query = substr($query,0,strlen($query)-2); # lop off the extra trailing comma
  $query .= " WHERE {$id_field}={$newid}";
  mysql_query($query);

  // return the new id
  return $newid;
}

Here is the link to the article http://www.epigroove.com/posts/79/how%5Fto%5Fduplicate%5Fa%5Frecord%5Fin%5Fmysql%5Fusing%5Fphp

Answer 7

If you know the name of the primary key column in your table (and I'm guessing that you do because it's in the where clause anyway), I think you could do this:

Edit - I forgot that MySQL doesn't support select...into. This should work instead

create new_table 
select * 
from   customer
where  customerid = 9181 

alter table new_table 
drop column customerid

insert into customer
select  null, *
from    new_table

drop table new_table