8

A great way to duplicate a row in MySQL is to use INSERT INTO ... SELECT FROM syntax.

For example:

INSERT INTO tblExample (col1, col2, col3)
SELECT col1, col2, col3 FROM tblExample WHERE pkey = 1234;

This is easy and straightforward, but from a code maintenance standpoint this is one more statement to keep track of if there are any schema changes. Suppose I add an additional column to the tblExample table, col4; now I have to remember to go back and update this SQL statement in my code. If I fail to do so, then I've just introduced a bug.

With this in mind, is there an easy to way to copy the whole row, whatever the schema may be, except for the primary key?

DOOManiac
  • 6,066
  • 8
  • 44
  • 67
  • You can probably issue a query to first get the schema, then dynamically build the insert. – gen_Eric Jul 13 '15 at 15:34
  • P.S. I assume you meant to write `INSERT INTO tblExample (col1, col2, col3)`? – gen_Eric Jul 13 '15 at 15:35
  • Thanks for pointing out the typo. Fixed. – DOOManiac Jul 13 '15 at 15:39
  • If you don't know the schema, you don't know what is the primary key. You can try to do this in PHP or any other server side script. Otherwise, if you find the answer, please let me know. – nl-x Jul 13 '15 at 15:42
  • "the schema, aside from the primary key" if you will. :) – DOOManiac Jul 13 '15 at 15:47
  • 2
    http://stackoverflow.com/questions/4039748/in-mysql-can-i-copy-one-row-to-insert-into-the-same-table – Joao Leal Jul 13 '15 at 15:48
  • Why don't you try to create a script that queries all columns except for the primary key and let it generate the SQL for you? – mr5 Jul 14 '15 at 02:22

2 Answers2

1

Very much against best practices you can do the following:

INSERT INTO myTable
SELECT * FROM myTable WHERE thisField = "abcd"
Tingo
  • 472
  • 7
  • 11
  • it works only if both the table have same schema – Hitesh Mundra Jul 13 '15 at 15:37
  • 2
    OP wants to duplicate a row from the same table - but not duplicate the primary key. Your answer will not do what is requested - this will fail due to duplicated PK. – PaulF Jul 13 '15 at 15:38
  • 2
    @PaulF, you're right, I didn't see the very last part about not duplicating the primary key. – Tingo Jul 13 '15 at 15:40
  • 1
    @PaulF could Tingo just add an `ON DUPLICATE KEY UPDATE` statement somehow? I'm not sure if that can be used dynamically (for all columns) or not. – AdamMc331 Jul 13 '15 at 15:57
  • 2
    @McAdam331: I think that would only update the duplicated row, it wouldn't insert an additional row. – PaulF Jul 13 '15 at 16:37
  • You're right, the answer is in the question, my mistake. – AdamMc331 Jul 13 '15 at 16:38
0

A somewhat inelegant way:

CREATE TEMPORARY TABLE tmpTable ENGINE=MEMORY SELECT * FROM realTable WHERE pk = 'something';
UPDATE tmpTable SET pk = 'something else' ;
INSERT INTO realTable SELECT * FROM tmpTable;
RandomSeed
  • 29,301
  • 6
  • 52
  • 87
  • 1
    This is quite strange a requirement. Duplicating a row is the last thing I would want to do in a database. A 1-n relationship could be a better option. – RandomSeed Jul 13 '15 at 22:49