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

sql

best-practices

performance

join

byNikita Barsukov·Jan 19, 2025

To duplicate a row in MySQL, use the INSERT INTO ... SELECT statement, excluding the autoincrement column. Copy all other column values seamlessly. Suppose you have a table, my_table, with an autoincrement id, to clone row with id=1:

INSERT INTO my_table (col1, col2)
SELECT col1, col2 FROM my_table WHERE id = 1;
-- Clone wars, this isn't. We're just copying rows here, Jedi style.

In this case, MySQL auto-generates a new id. Replace col1, col2 with your real column names, excluding the autoincrement id.

Decoding customized duplications for different scenarios

Depending on your use-case, sometimes you require customized columns when duplicating rows or dealing with specific conditions involving other constraints.

Retaining data consistency when manipulating column values

Modify specific columns when duplicating, to retain data consistency. It can be achieved inline in the SELECT statement.

INSERT INTO my_table (col1, col2, col3_to_change)
SELECT col1, col2, 'new_value' FROM my_table WHERE id = 1;
-- Change happens, as certain as taxes, even on cloned rows.

Handling BLOB/TEXT columns carefully like fragile glass

Working with tables containing BLOB/TEXT columns? Ensure your system's max_allowed_packet parameter accommodates the size of the data being inserted.

SET @@session.max_allowed_packet = 'your_desired_value';
-- 'Cos size does matter, especially when dealing with BLOBs.

Preventing Hanson-level repetitive duplication misuse

In systems with multiple user write access, implement checks or triggers to prevent unwanted or repetitive duplication.

Optimizing performance and safety using prepared statements

When performing duplication operations, prepared statements in stored procedures offer both performance and safety. Remember, as the Scouts say, safety (super) first!

Gaining advanced control of duplication like a skilled wizard

Sometimes, in-between levitating SQL queries and turning coffee into code, you need advanced level control, especially when dealing with tables having complex relations or constraints.

Employing NULL in autoincrement like a master trick

Set the autoincrement column to NULL explicitly to guarantee a new ID generation when copying:

INSERT INTO my_table (id, col1, col2)
SELECT NULL, col1, col2 FROM my_table WHERE id = 1;
-- Like erasing your data's past and giving it a new identity in witness protection!

Taking reins of autoincrement values like a seasoned knight

At times, assuming control of autoincrement column values becomes necessary:

SET @@SESSION.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
-- Then perform your insert query without modifying the autoincrement value.
-- Like riding a unicorn, once you control the autoincrement value, magic happens.

Do not forget to reset sql_mode after the operation.

Augmenting efficiency by avoiding temporary tables as easy as saying 'No'

Instead of spawning temporary tables unnecessarily, use INSERT INTO ... SELECT directly, saving resources just like conservationists save whales!

Exerting influence over SQL "taste, smell, touch"

Different SQL taste buds demand customization of the insert-select statement. Adjust the column list and subqueries/joins to cater to your application's requirements.

Duplication with WHERE clause for fussy eaters

To scarf down multiple specific rows, use a WHERE clause to specify the right filter :

INSERT INTO my_table (col1, col2)
SELECT col1, col2 FROM my_table WHERE col3 = 'some_criteria';
-- Like an all-you-can-eat buffet - but only the dishes you like.

Changing column values like a chameleon changes colors

Need to change data for the duplicated row? Specify the new values or expressions in the SELECT part:

INSERT INTO my_table (col1, col2, modified_date)
SELECT col1, col2, NOW() FROM my_table WHERE id = 1;
-- Because even SQL queries believe in "Be the change you want to see".