CODEWITHSUNDEEP
mysql
EddyTheB
EddyTheB

Reputation: 3200

Duplicate rows in mysql

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

Upvotes: 2

Views: 107

Answers (4)

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

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)

Upvotes: 1

tchow002
tchow002

Reputation: 1098

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.

Upvotes: 1

Chris
Chris

Reputation: 2806

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.

Upvotes: 2

mti2935
mti2935

Reputation: 12017

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.

Upvotes: 2

Related Questions