sql – How to copy a row and insert in same table with a autoincrement field in MySQL?

The Question :

254 people think this question is useful

In MySQL I am trying to copy a row with an autoincrement column ID=1 and insert the data into same table as a new row with column ID=2.

How can I do this in a single query?

The Question Comments :

The Answer 1

383 people think this answer is useful

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.

The Answer 2

51 people think this answer is useful

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.

The Answer 3

15 people think this answer is useful

Say the table is user(id, user_name, user_email).

You can use this query:

INSERT INTO user (SELECT NULL,user_name, user_email FROM user WHERE id = 1)

The Answer 4

12 people think this answer is useful

This helped and it supports a BLOB/TEXT columns.

CREATE TEMPORARY TABLE temp_table
AS
SELECT * FROM source_table WHERE id=2;
UPDATE temp_table SET id=NULL WHERE id=2;
INSERT INTO source_table SELECT * FROM temp_table;
DROP TEMPORARY TABLE temp_table;
USE source_table;

The Answer 5

8 people think this answer is useful

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.

The Answer 6

4 people think this answer is useful

A lot of great answers here. Below is a sample of the stored procedure that I wrote to accomplish this task for a Web App that I am developing:

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

-- Create Temporary Table
SELECT * INTO #tempTable FROM <YourTable> WHERE Id = Id

--To trigger the auto increment
UPDATE #tempTable SET Id = NULL 

--Update new data row in #tempTable here!

--Insert duplicate row with modified data back into your table
INSERT INTO <YourTable> SELECT * FROM #tempTable

-- Drop Temporary Table
DROP TABLE #tempTable

The Answer 7

4 people think this answer is useful

If you’re able to use MySQL Workbench, you can do this by right-clicking the row and selecting ‘Copy row’, and then right-clicking the empty row and selecting ‘Paste row’, and then changing the ID, and then clicking ‘Apply’.

Copy the row:

enter image description here

Paste the copied row into the blank row:

enter image description here

Change the ID:

enter image description here

Apply:

enter image description here

The Answer 8

3 people think this answer is useful

You can also pass in ‘0’ as the value for the column to auto-increment, the correct value will be used when the record is created. This is so much easier than temporary tables.

Source: Copying rows in MySQL (see the second comment, by TRiG, to the first solution, by Lore)

The Answer 9

2 people think this answer is useful
insert into MyTable(field1, field2, id_backup)
    select field1, field2, uniqueId from MyTable where uniqueId = @Id;

The Answer 10

0 people think this answer is useful

I was looking for the same feature but I don’t use MySQL. I wanted to copy ALL the fields except of course the primary key (id). This was a one shot query, not to be used in any script or code.

I found my way around with PL/SQL but I’m sure any other SQL IDE would do. I did a basic

SELECT * 
FROM mytable 
WHERE id=42;

Then export it to a SQL file where I could find the

INSERT INTO table (col1, col2, col3, ... , col42) 
VALUES (1, 2, 3, ..., 42);

I just edited it and used it :

INSERT INTO table (col1, col2, col3, ... , col42) 
VALUES (mysequence.nextval, 2, 3, ..., 42);

The Answer 11

0 people think this answer is useful

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.

The Answer 12

0 people think this answer is useful
INSERT INTO `dbMyDataBase`.`tblMyTable` 
(
    `IdAutoincrement`, 
    `Column2`, 
    `Column3`, 
    `Column4` 
) 

SELECT 
    NULL,  
    `Column2`, 
    `Column3`, 
    'CustomValue' AS Column4 
FROM `dbMyDataBase`.`tblMyTable` 
WHERE `tblMyTable`.`Column2` = 'UniqueValueOfTheKey' 
; 
/* mySQL 5.6 */

The Answer 13

0 people think this answer is useful

Try this:

INSERT INTO test_table (SELECT null,txt FROM test_table)

Every time you run this query, This will insert all the rows again with new ids. values in your table and will increase exponentially.

I used a table with two columns i.e id and txt and id is auto increment.

The Answer 14

-2 people think this answer is useful

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

Add a Comment