How to Duplicate Rows in MySQL Without a Duplicate Entry Error?

Duplicating Rows in MySQL: An Alternative Solution

When attempting to duplicate a row within the same MySQL table using the query:

insert into table select * from table where primarykey=1

you may encounter a duplicate entry error due to the existing primary key constraint. While a temporary table can be used to circumvent this issue, there's a simpler solution.

Using the following modified technique inspired by Leonard Challis:

CREATE TEMPORARY TABLE tmptable_1 SELECT * FROM table WHERE primarykey = 1;
UPDATE tmptable_1 SET primarykey = NULL;
INSERT INTO table SELECT * FROM tmptable_1;
DROP TEMPORARY TABLE IF EXISTS tmptable_1;

Explanation:

1. A temporary table (tmptable_1) is created and populated with the desired row to be duplicated.
2. The primarykey column in the temporary table is set to NULL. This allows MySQL to automatically assign a unique value, avoiding any key duplication.
3. The row from the temporary table is inserted into the original table.
4. The temporary table is then dropped.

By setting the primarykey to NULL, you eliminate the risk of creating duplicate entries. Additionally, adding LIMIT 1 to the end of the INSERT INTO statement ensures that only one row is inserted.

Appending the primary key value to the temporary table name is a precautionary measure to avoid potential conflicts when multiple rows are being duplicated simultaneously.

The above is the detailed content of How to Duplicate Rows in MySQL Without a Duplicate Entry Error?. For more information, please follow other related articles on the PHP Chinese website!