Duplicate database row using ID

Question

Is it possible to simply duplicate a database record based on this identifier without indicating that all field names for this row should be duplicated.

I have a line that looks like

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

I would like to make a duplicate. id - auto increment. The easiest way to do this.

Answer 1

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

Sort of:

 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 data shielding.

Answer 2

The clearest way to show what you are doing is to explicitly specify the columns. This ensures that you get the desired functionality, as well as clarity for those who need to change 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 really want to insert records dynamically due to unknown fields, you will need to use dynamic SQL and data from INFORMATION_SCHEMA.COLUMNS and create the field names yourself in a loop or similar structure.

Answer 3

If you do not want to list all the fields that you must play using 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 even convert it to a stored procedure by passing it three parameters (id, table name, and database name) as follows:

 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'); 

Answer 4

I also like to use another way. In the case of security measures, when the MySQL db user account does not have DROP permissions:

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

Put the results in an array, and then insert the array into the database. It also helps if you want to change the name to include - Copy after it.

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

Alternates can be accessories for variable arrays ($ name - where $ name = $ array [0]). This can be a few steps, but in the case of copying, you are most likely copying one line. If there are several lines, this may be a slower process, then other answers.