Geek Answers Handbook

Duplicate a record in MySQL

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

Here is something more complex than I originally thought, all I have to do is copy the entire record to the new record in the auto-increment table in MySql without having to specify each field. This is because the table may change in the future and may break duplication. I will duplicate MySQL records from PHP.

This is a problem because in the "SELECT *" query, MySql will try to copy the identifier of the record being copied, which generates a duplicate identification error.

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

Is there any way to do this with PHP or MySQL?

+5
source share
6 answers

I finally found this code. I am sure that this will help people in the future. So there 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

+12
source

What about

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

It seems to work for me. Replace the table, fields, state, of course.

The null value allows the database to generate an auto-increment identifier for you.

+1
source

:

, . , .

+1

temp, ? , mysql , .

0

, : PHP . , "-", , . *.

0

( , , where), , :

- , MySQL select... in.

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
0

More articles:


All Articles