Duplicate rows in mysql

Question

Maybe this seems like the opposite of what people would normally like to do, but I'd like to duplicate rows in my mysql table.

So, for example, I have a table with 1 row that has project_id, and one row with file_name. Each project has very many files associated with it. I would like to duplicate all rows that exist for one project in to another project. I could write some python script that would do this but I suspect there would be a way to do it from within mysql.

Any ideas? Thanks.

For example, if I have a project that looks like this...

file_id, file_name, project_id
-------, ---------, ----------
      1, file1.png,          1
      2, file2.png,          1
      3, file3.png,          1

...and up to around 100 rows.

What should I type in to get a project that looks like this...

file_id, file_name, project_id
-------, ---------, ----------
      1, file1.png,          1
      2, file2.png,          1
      3, file3.png,          1
      4, file1.png,          2
      5, file2.png,          2
      6, file3.png,          2

Can you provide a sample table to show what results you wish to achieve? — hjpotter92, Jul 27 '13 at 00:30

score 2 · Answer 1 · edited Jul 27 '13 at 00:32

2

In MySQL, you can combine an insert statement with a select statement, such that the values inserted in the new row by the insert statement are the ones selected by the select statement. See INSERT..SELECT for details.

edited Jul 27 '13 at 00:32

user2246674

7,621
25
28

answered Jul 27 '13 at 00:31

mti2935

11,465
3
29
33

score 2 · Answer 2 · edited May 23 '17 at 12:29

2

It sounds like what you need is to INSERT from a SELECT. The basic idea for doing this can be found from the link below.

mysql -> insert into tbl (select from another table) and some default values

Side note: If you duplicate the rows completely then you will probably have issues with database keys. If you don't, then your database has design issues.

edited May 23 '17 at 12:29

Community

1
1

answered Jul 27 '13 at 00:33

Chris

2,766
1
29
34

Thanks, that gave me something to look in to, but I didn't succeed... I'm not sure how to combine the records from the original rows with other values. Do you mind giving me an example? I provided a simplified example of my table above. – EddyTheB Jul 28 '13 at 05:10

score 1 · Accepted Answer · answered Jul 28 '13 at 05:15

1

Try this:

INSERT INTO table_name (file_name, project_id)
SELECT file_name, project_id + 1 FROM table_name;

This selects everything currently in the table named table_name and reinserts the records into table_name with an incremented project_id. It assumes the file_id field is auto incremented.

answered Jul 28 '13 at 05:15

tchow002

1,068
6
8

Awesome, thanks. I had to adapt it to "SELECT file_name, project_id + 8 FROM table_name WHERE project_id = 1", but it was getiing the answers from SELECT to sit alongside a project_id of 9 that I'd struggled with and it works now. Thanks. – EddyTheB Jul 28 '13 at 14:55

score 1 · Answer 4 · answered Jul 28 '13 at 05:22

1

I think it can be resolve this way, in case you have a main table that holding the entire project id list, project_table in my example, and assuming that file_id in the file_table is set to auto_increment :

INSERT INTO file_table ( file_name, project_id)
VALUES (SELECT file_name, project_table.project_id
        FROM project_table
        INNER JOIN file_table ON project_id = 1
        WHERE project_table.id != 1)

answered Jul 28 '13 at 05:22

Fabien TheSolution

5,055
1
18
30

Thanks for your help, I tried Tanvir's solution first because it was simpler, and it worked, so I haven't tried yours. But I appreciate your help. – EddyTheB Jul 28 '13 at 14:56

Duplicate rows in mysql

4 Answers4