How to copy a row and insert in same table with a

Question

I have a table Eg- tab . What I am trying to do is copying a row with an autoincrement column ID=1 and insert the data into same table with a row and column ID=2.

Using MySql. How can I do this in a single query?Please help

Answer 1

I tend to use a variation of what mu is too short posted:

INSERT INTO something_log
SELECT NULL, s.*
FROM something AS s
WHERE s.id = 1;

As long as the tables have identical fields (excepting the auto increment on the log table), then this works nicely.

Since I use stored procedures whenever possible (to make life easier on other programmers who aren't too familiar with databases), this solves the problem of having to go back and update procedures every time you add a new field to a table.

It also ensures that if you add new fields to a table they will start appearing in the log table immediately without having to update your database queries (unless of course you have some that set a field explicitly)

Warning: You will want to make sure to add any new fields to both tables at the same time so that the field order stays the same... otherwise you will start getting odd bugs. If you are the only one that writes database interfaces AND you are very careful then this works nicely. Otherwise, stick to naming all of your fields.

Note: On second thought, unless you are working on a solo project that you are sure won't have others working on it stick to listing all field names explicitly and update your log statements as your schema changes. This shortcut probably is not worth the long term headache it can cause... especially on a production system.

Answer 2

Dump the row you want to sql and then use the generated SQL, less the ID column to import it back in.

Answer 3

Use INSERT ... SELECT:

insert into your_table (c1, c2, ...)
select c1, c2, ...
from your_table
where id = 1

where c1, c2, ... are all the columns except id. If you want to explicitly insert with an id of 2 then include that in your INSERT column list and your SELECT:

insert into your_table (id, c1, c2, ...)
select 2, c1, c2, ...
from your_table
where id = 1

You'll have to take care of a possible duplicate id of 2 in the second case of course.

Answer 4

For a quick, clean solution that doesn't require you to name columns, you can use a prepared statement as described here: https://stackoverflow.com/a/23964285/292677

If you need a complex solution so you can do this often, you can use this procedure:

DELIMITER $$

CREATE PROCEDURE `duplicateRows`(_schemaName text, _tableName text, _whereClause text, _omitColumns text)
SQL SECURITY INVOKER
BEGIN
  SELECT IF(TRIM(_omitColumns) <> '', CONCAT('id', ',', TRIM(_omitColumns)), 'id') INTO @omitColumns;

  SELECT GROUP_CONCAT(COLUMN_NAME) FROM information_schema.columns 
  WHERE table_schema = _schemaName AND table_name = _tableName AND FIND_IN_SET(COLUMN_NAME,@omitColumns) = 0 ORDER BY ORDINAL_POSITION INTO @columns;

  SET @sql = CONCAT('INSERT INTO ', _tableName, '(', @columns, ')',
  'SELECT ', @columns, 
  ' FROM ', _schemaName, '.', _tableName, ' ',  _whereClause);

  PREPARE stmt1 FROM @sql;
  EXECUTE stmt1;
END

You can run it with:

CALL duplicateRows('database', 'table', 'WHERE condition = optional', 'omit_columns_optional');

Examples

duplicateRows('acl', 'users', 'WHERE id = 200'); -- will duplicate the row for the user with id 200
duplicateRows('acl', 'users', 'WHERE id = 200', 'created_ts'); -- same as above but will not copy the created_ts column value    
duplicateRows('acl', 'users', 'WHERE id = 200', 'created_ts,updated_ts'); -- same as above but also omits the updated_ts column
duplicateRows('acl', 'users'); -- will duplicate all records in the table

DISCLAIMER: This solution is only for someone who will be repeatedly duplicating rows in many tables, often. It could be dangerous in the hands of a rogue user.

Answer 5

insert into MyTable(field1, field2, id_backup)
    select field1, field2, uniqueId from MyTable where uniqueId = @Id;

Answer 6

IMO, the best seems to use sql statements only to copy that row, while at the same time only referencing the columns you must and want to change.

CREATE TEMPORARY TABLE temp_table ENGINE=MEMORY

SELECT * FROM your_table WHERE id=1;
UPDATE temp_table SET id=NULL; /* Update other values at will. */

INSERT INTO your_table SELECT * FROM temp_table;
DROP TABLE temp_table;

See also av8n.com - How to Clone an SQL Record

Benefits:

• The SQL statements 2 mention only the fields that need to be changed during the cloning process. They do not know about – or care about – other fields. The other fields just go along for the ride, unchanged. This makes the SQL statements easier to write, easier to read, easier to maintain, and more extensible.
• Only ordinary MySQL statements are used. No other tools or programming languages are required.
• A fully-correct record is inserted in your_table in one atomic operation.