Copying MySQL parent and all related child records in database to a new ID

Question

Is there an easy way to copy parent records and all related child records using strictly SQL without using cursors or external scripts/code? Here's an example of what I've got:

categories
==
category_id
category_name

parent_table
==
parent_record_id
category_id
... <other fields>

child_table1
==
child_table1_id
parent_record_id
... <other fields>

child_table2
==
child_table2_id
parent_record_id
... <other fields>

Basically, I need to make an exact duplicate of all this data. The only fields that will change are the IDs since they are all auto_increment. Based upon the schema above, when I copy the records in the categories table I'll end up with all new category_id values. When I create duplicates of the parent_table records I'll need a way to somehow update the old category_id values with the new category_id values that were just created. I'll then need to use the new parent_record_id values when copying the child_record1 and child_record2 records.

I feel like there must be an easier way to accomplish this rather than scripting it all out externally in PHP or another language. Is there?

Answer 1

Check out this article, http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

You can basically do the following

INSERT INTO table (id, column1, column2) (SELECT NULL, column1, column2 FROM table WHERE whatever_id = 123);