CODEWITHSUNDEEP
mysqlsqlinsert
RichM
RichM

Reputation: 272

Duplicating records in the same MySQL table has duplicate entry for key

Is there a way to use a MySQL INSERT similar to the following:

INSERT INTO doc_details SELECT * FROM doc_details WHERE dd_id = 1

This doesn't work because the primary key is being repeated and it can get very long-winded expanding the columns out.

The purpose of this is to duplicate rows in the same table which will get modified later, retrieving the last_insert_id for the new record. So ideas for other ways to do this would be appreciated too.

Thanks.

Upvotes: 0

Views: 172

Answers (4)

RichM
RichM

Reputation: 272

Thanks for the answers. Really appreciated. Because most answers specify the column, this led to some extra research that said 'wildcards cannot be used in INSERT statements. Select, Modify and insert into the same table

I managed to solve this in my application with a separate SELECT then the INSERT with the columns expanded with a Perl map function:

SELECT * FROM doc_details WHERE dd_id = 1

Then in Perl, with the row as a hash reference in $data:

$data->{'dd_id'} = 0;
$columns = join(',', map {$_ .'='. $dbh->quote( $data->{$_} ) } keys %{$cdh} );

Does the trick nicely - it copies the row regardless of changes to the column structure/order as long as the auto_increment column is maintained.

I know it's not a pure SQL solution - although Ravinder provided one that was.

Thanks to all!

Upvotes: 0

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

You can depend on temporary table to copy from old record and omitting the key field value.
You have to use at least one named column, i.e. the key field name, to omit its repeating values.

See the following example: 

CREATE TEMPORARY TABLE tmp SELECT * from doc_details WHERE dd_id = ?;
ALTER TABLE tmp drop pk_field_name_here; -- drop the key field for not repeating

INSERT INTO doc_details SELECT 0, tmp.* FROM tmp;
DROP TABLE tmp;

You can observe that no other filed names are used but the key field name to omit it's value.

You can also refer to my answer to a similar posting at: Mysql: Copy row but with new id.

Upvotes: 0

Francois Bourgeois
Francois Bourgeois

Reputation: 3690

Simply name the columns you want to duplicate and omit the primary key:

INSERT INTO doc_details (col1, col2, col3)
SELECT col1, col2, col3 
FROM doc_details 
WHERE dd_id = 1

Upvotes: 1

Devart
Devart

Reputation: 121922

I'd suggest you to make ID field with AUTO_INCREMENT option, then use NULL values when inserting -

INSERT INTO doc_details(id, column1, column2)
  SELECT NULL, column1, column2 FROM doc_details WHERE dd_id = 1;

In this case old ID will be changed with new ones.

Upvotes: 0

Related Questions