How to Duplicate a Row in a Table Containing an Auto-Increment Field in MySQL

1. Overview

The MySQL relational database uses its auto-increment feature to automatically generate a unique, ascending sequence of integer values for a column when we add a new row of data. The database adds a value to any auto-increment column for us.

In this tutorial, we’ll learn how to duplicate a row in a table containing an auto-increment field in MySQL and how MySQL works to avoid collisions.

2. Setting Up Schema and Table

At the outset, note that only one column in a table can be the auto-increment column, and it must be a key (like PRIMARY KEY, UNIQUE, or INDEX).

To demonstrate, we’ll use tables from the University database. However, because the default tables don’t have the auto-increment feature enabled, let’s create a new table with an auto-increment column called id using an existing table called department as a template.

CREATE TABLE department_copy 
TABLE department;

DELETE 
FROM department_copy;

ALTER TABLE department_copy 
MODIFY COLUMN id INT UNIQUE NULL AUTO_INCREMENT;

To determine whether a new or existing table has an auto-increment column, we need to run a query on the table:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'department_copy'
AND EXTRA LIKE '%auto_increment%';

The output should list the auto-increment column if the table has one:

+-------------+
| COLUMN_NAME |
+-------------+
| id          |
+-------------+

To demonstrate copying a row of table data, let’s add at least a row of table data:

INSERT INTO department_copy (name, code) 
VALUES 
("Computer Science", "CS");

Let’s query the table to be sure:

SELECT * from department_copy;
+----+--------------------------------+------+
| id | name                           | code |
+----+--------------------------------+------+
|  1 | Computer Science               | CS   |
+----+--------------------------------+------+

We’ll use this row of data to demonstrate copying and inserting in the same table.

3. Using an INSERT … SELECT Statement

We can use an INSERT … SELECT statement to copy and insert a row within the same table.
Let’s duplicate the row of data with id 1:

INSERT INTO department_copy (name, code) 
SELECT name, code 
FROM department_copy 
WHERE id = 1;

And now we’ll verify that it copies and inserts the row:

SELECT * from department_copy;
+----+------------------+------+
| id | name             | code |
+----+------------------+------+
|  1 | Computer Science | CS   |
|  2 | Computer Science | CS   |
+----+------------------+------+

Note that the new row gets a new id value, auto-incremented to 2.

4. Duplication Scenarios

Let’s discuss a few scenarios we may encounter while duplicating a row within a single table.

4.1. Inserting the Duplicated Row With a Specific Auto-Increment Column Value

We didn’t specify a value for the auto-increment column id in the preceding example; therefore, it got the next value in the auto-increment sequence, which is 2. Alternatively, we have the option to provide a specific value for the auto-incremented column, id.

Let’s duplicate the row of data at id 1 as a new row with id 11:

INSERT INTO department_copy (id, name, code) 
SELECT 11, name, code 
FROM department_copy 
WHERE id = 1;

And let’s verify the new row:

SELECT * from department_copy;
+----+------------------+------+
| id | name             | code |
+----+------------------+------+
|  1 | Computer Science | CS   |
|  2 | Computer Science | CS   |
| 11 | Computer Science | CS   |
+----+------------------+------+

Indeed, it adds a new row with id 11.  We should expect this because we explicitly provide an id value.

The auto-increment sequence, however, gets reset when we add a new row of data. This means that when we add additional rows of data without providing an explicit value for the id column, the automatically generated values for the id column start using the reset auto-increment sequence.

Let’s demonstrate by adding a few more rows:

INSERT INTO department_copy (name, code)
VALUES ("Electronics and Communications", "EC"),
       ("Mechanical Engineering", "ME"),
       ("Civil Engineering", "CE"),
       ("Mathematics", "MA");

If we query the table again:

SELECT * from department_copy;

then the new rows of data continue after id 11:

+----+--------------------------------+------+
| id | name                           | code |
+----+--------------------------------+------+
|  1 | Computer Science               | CS   |
|  2 | Computer Science               | CS   |
| 11 | Computer Science               | CS   |
| 12 | Electronics and Communications | EC   |
| 13 | Mechanical Engineering         | ME   |
| 14 | Civil Engineering              | CE   |
| 15 | Mathematics                    | MA   |
+----+--------------------------------+------+

This may not be what we expected. Let’s discuss another scenario that may be unexpected.

4.2. Inserting the Duplicated Row at a Gap in the Auto-Increment Sequence

To extend the same example, let’s say we want to duplicate an existing row and add it at a gap in the auto-increment sequence. Note the gap in the sequence between id values 2 and 11.

Let’s duplicate the row of data at id 1 as a new row with id 5:

INSERT INTO department_copy (id, name, code) 
SELECT 5, name, code 
FROM department_copy 
WHERE id = 1;

Indeed, it  duplicates the new row into the gap:

+----+--------------------------------+------+
| id | name                           | code |
+----+--------------------------------+------+
|  1 | Computer Science               | CS   |
|  2 | Computer Science               | CS   |
|  5 | Computer Science               | CS   |
| 11 | Computer Science               | CS   |
| 12 | Electronics and Communications | EC   |
| 13 | Mechanical Engineering         | ME   |
| 14 | Civil Engineering              | CE   |
| 15 | Mathematics                    | MA   |
+----+--------------------------------+------+

Again, this is because we explicitly provide an id value.

We may also expect the auto-increment sequence to get reset to 6. Let’s find by duplicating a row:

INSERT INTO department_copy (name, code) 
SELECT name, code 
FROM department_copy
WHERE id = 1;

By querying again:

SELECT * from department_copy;

we can see it begins with id 16, continuing in the auto-increment sequence:

+----+--------------------------------+------+
| id | name                           | code |
+----+--------------------------------+------+
|  1 | Computer Science               | CS   |
|  2 | Computer Science               | CS   |
|  5 | Computer Science               | CS   |
| 11 | Computer Science               | CS   |
| 12 | Electronics and Communications | EC   |
| 13 | Mechanical Engineering         | ME   |
| 14 | Civil Engineering              | CE   |
| 15 | Mathematics                    | MA   |
| 16 | Computer Science               | CS   |
+----+--------------------------------+------+

This is because it resets the auto-increment sequence only when we add a new row of data with an auto-increment column value higher or greater than the current value of the sequence counter.

4.3. Inserting the Duplicated Row After Deleting the Last Row

Again, extending the same example, let’s say we delete the last row and subsequently want to reduplicate a row with the same id. As an example, let’s delete the row at id 16 and try reduplicating it.

Let’s start by deleting the row:

DELETE 
FROM department_copy 
WHERE id = 16;

Next, let’s use the INSERT … SELECT statement to duplicate the row at id 1:

INSERT INTO department_copy (id, name, code) 
SELECT name, code 
FROM department_copy 
WHERE id = 1;

Indeed, it duplicates the new row; however, is it at id 16? Let’s find out:

SELECT * from department_copy;

As we can see, it adds a new row of data with id of 17:

+----+--------------------------------+------+
| id | name                           | code |
+----+--------------------------------+------+
|  1 | Computer Science               | CS   |
|  2 | Computer Science               | CS   |
|  5 | Computer Science               | CS   |
| 11 | Computer Science               | CS   |
| 12 | Electronics and Communications | EC   |
| 13 | Mechanical Engineering         | ME   |
| 14 | Civil Engineering              | CE   |
| 15 | Mathematics                    | MA   |
| 17 | Computer Science               | CS   |
+----+--------------------------------+------+

This is because when we delete rows of data with auto-generated column values, it doesn’t reset the auto-increment sequence.

Then, how do we duplicate a new row at id 16 after deleting the row at id 16? We can reduplicate a row with the same id that was deleted. We do this by resetting the auto-increment sequence with an ALTER TABLE statement.

We’ll delete the row at id 17 and reset the auto-increment sequence to 16:

DELETE 
FROM department_copy 
WHERE id = 17;

ALTER 
TABLE department_copy 
AUTO_INCREMENT = 16;

Let’s try to duplicate a row of data one more time:

INSERT INTO department_copy (id, name, code) 
SELECT name, code 
FROM department_copy 
WHERE id = 1;

Indeed, it adds a new row with id 16:

+----+--------------------------------+------+
| id | name                           | code |
+----+--------------------------------+------+
|  1 | Computer Science               | CS   |
|  2 | Computer Science               | CS   |
|  5 | Computer Science               | CS   |
| 11 | Computer Science               | CS   |
| 12 | Electronics and Communications | EC   |
| 13 | Mechanical Engineering         | ME   |
| 14 | Civil Engineering              | CE   |
| 15 | Mathematics                    | MA   |
| 16 | Computer Science               | CS   |
+----+--------------------------------+------+

We can use the LAST_INSERT_ID() function when we don’t know the auto-increment sequence setting. Accordingly, it returns the first automatically generated value added to an auto-increment column as a result of the last INSERT statement:

SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|               16 |
+------------------+

4.4. Inserting the Duplicated Row With a Duplicate Value

There’s one more duplication scenario we need to be aware of. We can’t insert the duplicated row using the id of the row that we duplicated. Let’s demonstrate with an example:

INSERT INTO department_copy (id, name, code) 
SELECT 1, name, code 
FROM department_copy 
WHERE id = 1;

This time, we get an error message:

ERROR 1062 (23000): Duplicate entry '1' for key 'department_copy.id'

We can avoid this error by using the auto-increment feature to automatically generate values, or by specifying an explicit id value that doesn’t already exist.

5. Conclusion

In this article, we learned about duplicating a row in a table that has the auto-increment feature enabled in MySQL. We use the INSERT … SELECT statement. Importantly, we need to be aware of certain scenarios that may produce unexpected results.