How to Insert Duplicate Rows into a MySQL Table Without Listing Columns?

Copying Rows into Same MySQL Table Without Column Listing

In MySQL, one can encounter the error "Duplicate entry 'xxx' for key 1" when attempting to insert a row into a table that already contains the same primary key value. This is because MySQL enforces unique values for primary keys.

To resolve this issue while avoiding manual listing of all columns, a simpler method exists:

1. Create a temporary table with the same schema as the original table:

CREATE TEMPORARY TABLE tmptable_1 SELECT * FROM table WHERE primarykey = 1;

2. Update the primary key column in the temporary table to NULL:

UPDATE tmptable_1 SET primarykey = NULL;

3. Insert the modified row into the original table:

INSERT INTO table SELECT * FROM tmptable_1;

4. Drop the temporary table:

DROP TEMPORARY TABLE IF EXISTS tmptable_1;

By setting the primary key to NULL in the temporary table, MySQL can auto-generate a new unique value when inserting the row into the main table, thus avoiding the duplicate entry error.

For added assurance, consider adding a LIMIT 1 clause to the end of the INSERT INTO line to ensure only a single row is being inserted.

The above is the detailed content of How to Insert Duplicate Rows into a MySQL Table Without Listing Columns?. For more information, please follow other related articles on the PHP Chinese website!