4

Can we simply duplicate an entry in the database based on it's ID without having to mention all the field names for that row to be duplicated.

I have a row that looks like 

id name last city state whatever
1  joe  doe  xyz  NY    yyy

I would like to make a duplicate. id is auto increment. What is the easiest way to do this.

Pinkie
  • 10,126
  • 22
  • 78
  • 124
  • please elaborate on how you want to do it? sql, php page, exe, nightly job, etc. – Robert Nov 28 '11 at 22:11
  • See here: http://stackoverflow.com/questions/729489/duplicate-copy-records-in-the-same-mysql-table – AR. Nov 28 '11 at 22:53

4 Answers4

2

The most clear way to show what you're doing is to name the columns out explicitly. This ensures that you get the functionality you desire, as well being clear for anyone who has to modify this code after you.

INSERT INTO yourTable(name, last, city, state, whatever)
SELECT name, last, city, state, whatever
FROM yourTable
WHERE id = 1

If you truly want to do insert records dynamically due to unknown fields, you will need to use dynamic SQL and data from INFORMATION_SCHEMA.COLUMNS and build up the field names yourself in a loop, or similar structure.

Adam Wenger
  • 17,100
  • 6
  • 52
  • 63
  • Thanks but In my question i asked if there is a way to do it without having to mention all the field names – Pinkie Nov 28 '11 at 22:14
  • I don't think you can do it without calling out the columns since ID is an auto increment you can't just do and insert into yourTable() because you don't want the auto increment to populate with the same value. – Robert Nov 28 '11 at 22:18
  • 3
    @Pinkie , do you have to pay for keystrokes ? – tereško Nov 28 '11 at 22:19
  • Customer can dynamically add/remove fields from admin area. I want the duplicate function to also be dynamic without having to mentione any field names. – Pinkie Nov 28 '11 at 22:23
  • @Pinkie I've updated my answer to mention dynamic SQL and the `INFORMATION_SCHEMA` view to address the truly dynamic request – Adam Wenger Nov 28 '11 at 22:27
  • @Pinkie , if customer can dynamically add new fields , then you should not keep each item in a separate column. Instead create a separate table like `Details` tied via many-to-many to your `Customers`. – tereško Nov 28 '11 at 22:40
2

If you don't want to list all fields you have to play with the information_schema and prepared statements.

This is an example

set @str = (select concat('insert into ', table_name,' (', group_concat(column_name),')',' select ',group_concat(column_name),' from ', table_name, ' where id = 1') from 
information_schema.columns
where table_schema = 'your_db' and table_name = 'your_table'
and column_key <> 'PRI');
prepare stmt from @str;
execute stmt;
deallocate prepare stmt;

You can convert it even in a stored procedure passing it three parameters (id, table name and database name) in this way:

delimiter //
drop procedure if exists copy_record //
create procedure copy_record(in my_id int, in my_db varchar(50), in my_table varchar(50) )
begin
set @str = (select concat('insert into ', table_name,' (', group_concat(column_name),')',' select ',group_concat(column_name),' from ', table_name, ' where id = ',my_id) from 
information_schema.columns
where table_schema = my_db and table_name = my_table
and column_key <> 'PRI');
prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
end; //
delimiter ;

call copy_record(1,'db_name','table_name');
Nicola Cossu
  • 54,599
  • 15
  • 92
  • 98
2

Based on your comment on @Adam Wenger's answer, I would just read all fields in one query and then build a new query skipping the id field.

Something like:

SELECT * FROM table_name WHERE id=some_id

php:

$sql = "INSERT INTO table_name SET ";
$first = true;
foreach ($row as $key => $value)
{
  if ($key != 'id')
  {
     if ($first) 
     {
        $first = false;
     }
     else
     {
       $sql .= ", ";
     }
     $sql .= '`' . $key . "`='" . $value . "'";
  }
}
// run query

By the way, I would generate a prepared statement in PDO for both queries so that I would not have to worry about escaping the data.

jeroen
  • 91,079
  • 21
  • 114
  • 132
0

I also like to use a different way. In case of security measures where MySQL db user account doesn't have DROP permissions:

SELECT `col1`, `col2`, `col3` FROM `table`

Put results into an array and then insert the array into the database. Also helps if you want to modify a name to include - Copy after it.

INSERT INTO `table` (`col1`, `col2`, `col3`) VALUES (?,?,?)

The placeholders can be variable array accessors ($name -- where $name = $array[0]). This may be multiple steps but in the case of copying you are most likely copying a single row. If there are multiple rows it could be a slower process then the other answers.

user1522901
  • 109
  • 8