0

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.

RichM
  • 272
  • 1
  • 2
  • 14
  • 1
    You read this? http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html – Ron van der Heijden Nov 29 '12 at 12:57
  • I find below link this may be solution for you. http://stackoverflow.com/questions/545593/how-to-insert-with-where-clause – Praveen D Nov 29 '12 at 13:06
  • Not sure I like this idea, but if you must do it then I think you could automatically generate your INSERT / SELECT statement (as given by Devart below) using the INFORMATION_SCHEMA. Based it on something like this - http://stackoverflow.com/questions/9122/select-all-columns-except-one-in-mysql – Kickstart Nov 29 '12 at 13:28

4 Answers4

1

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
Francois Bourgeois
  • 3,650
  • 5
  • 30
  • 41
0

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.

Devart
  • 119,203
  • 23
  • 166
  • 186
  • Thanks, I appreciate this is a very good way to do it. However, I've got good reason not to want to specify the columns individually. Firstly it's still in development and often changing and secondly is for future development not needing to modify too many SQL statements. – RichM Nov 29 '12 at 13:12
  • Do you need old `ID` values? – Devart Nov 29 '12 at 13:25
0

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.

Community
  • 1
  • 1
Ravinder Reddy
  • 23,692
  • 6
  • 52
  • 82
  • Thanks, this is the sort of thing I was thinking of because the columns don't need to be specified specifically. – RichM Dec 05 '12 at 11:11
0

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!

Community
  • 1
  • 1
RichM
  • 272
  • 1
  • 2
  • 14
  • I didn't select my own answer because I think it's best... but simply because it's the one I used. – RichM Dec 05 '12 at 11:15