CODEWITHSUNDEEP
phpsqlmysql
Pasta
Pasta

Reputation: 2491

Duplicate a record in MySQL

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?

Upvotes: 5

Views: 13574

Answers (7)

Anoop P S
Anoop P S

Reputation: 782

There is an easy way, to create duplicate record in same table test. in my local

  1. Select column_name,data_type from information_schema.columns where table_name = 'your_table_name' AND column_name != 'id'
  2. Prepare the column_name array and join using "," to create an Insert statement like below
  3. INSERT INTO table2 (column1, column2, column3, ...) SELECT column1, column2, column3, FROM table1
  4. Perform Query insert . this will work easily

Upvotes: 0

Justin Gallagher
Justin Gallagher

Reputation: 3232

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

Upvotes: 0

Pasta
Pasta

Reputation: 2491

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_to_duplicate_a_record_in_mysql_using_php

Upvotes: 13

Bill Karwin
Bill Karwin

Reputation: 562348

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.

Upvotes: 1

Smandoli
Smandoli

Reputation: 7019

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 *.

Upvotes: 0

itsmatt
itsmatt

Reputation: 31406

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.

Upvotes: 1

Peter Recore
Peter Recore

Reputation: 14187

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.

Upvotes: 0

Related Questions