Easiest way to duplicate rows in a table, subtable, and subsubtable

Question

I am implementing a "Save As Copy" function for a small web app that uses MySQL.

Let's say I have three tables, like so...

TABLE Doc
 ID,
 title,
 text

TABLE DocAttributes
 ID,
 DocID -> Doc(ID)
 title,
 text

TABLE DocSubAttributes
 DocAttrID -> DocAttributes(ID)
 title,
 text

What we have here is a situation where a single Document can have multiple DocAttributes, and each DocAttribute can in turn have multiple SubAttributes.

Now, if I did not have to worry about the DocSubAttributes table, this would be fairly straightforward, and I would do something like this...

$insertID = INSERT INTO Doc (title, text) SELECT title, text FROM Doc WHERE ID = $docID;

INSERT INTO DocAttributes DocID, title, text SELECT $insertID AS DocID, title, text FROM Doc WHERE ID = $docID;

However, because there is a third one-to-many table, this method doesn't work. I need to know the primary keys for each DocAttributes row for creating the corresponding rows in DocSubAttributes.

The only way I am aware of to do this is to iterate through DocAttributes one row at a time, selecting all DocSubAttributes with each iteration, and then doing each insert, again one at a time, iteratively.

I am wondering: Is there a simpler way to duplicate these rows in their respective tables to create an entirely new and separate data entity without relying on iteration?

Thanks for your help

Answer 1

If you are willing to add ID fields to your tables that link back to the master record, you should be able to accomplish it with three INSERT queries (the last of which would have a JOIN to get the new ID).

Answer 2

You could make your own primary key before inserting like, for example,

$uniqueKey = md5($ID . $title . $text);

And insert that as DocAttributes primary key (ID) which you'll now know and use for inserting DocSubAttributes