A common piece of functionality found in web apps is the ability to duplicate an object. By duplicate, I mean the user clicks on a button, and a copy of the item is made in their account. By object, I mean a post, form, report, page, etc. It’s usually a pretty easy process that usually requires the duplication of some rows in a database.

Looking through source code, I have noticed a few times when developers loop through each row, and duplicate them individually. For example:

$sql = 'SELECT * FROM Comments WHERE PostId = 1';
$rs = $db->query($sql);
$while($row = mysql_fetch_array($rs, MYSQL_ASSOC)) {
    // reinsert into table with a new query per iteration

If there are 10 comments, the loop will go through 10 queries. I wanted to make note to anyone who wasn’t aware that this can be done entirely with SQL without the need for logic in the server side code.

$sql = 'INSERT INTO Comments(Message, Typeof, PostId) ' .
       'SELECT Message, Typeof, '.$dynamic.' FROM Comments WHERE PostId = 1';

If there are 100 records with a PostId of 1, then 100 new records will be inserted. I also placed a variable named dynamic in there to show that you can also modify the data that gets copied into the new records. In this example, you can imagine that we are copying all comments from one post to another. The first Post has a PostId of 1, and the second of 2. If we set dynamic to 2, we will achieve what we’re looking for.

HTML Form Builder
Ryan Campbell

Duplicating Records With SQL by Ryan Campbell

This entry was posted 3 years ago and was filed under Notebooks.
Comments are currently closed.


  1. Patrick Lee · 3 years ago

    Great tip. SQL is powerful stuff. Like you said, not much difference for a few rows, but definitely something to add to a list of best practices.

  2. Paolo · 3 years ago

    Just a few hours ago I try to make the same thing in a trigger but MySQL doesn’t support a subquery (a select) into the same table of the primary query (an insert). This suck a lot!

  3. Bramus! · 3 years ago

    Heh, looked that up exactly one week ago: http://www.2hufters.be/archives/81 Great minds think alike? Or was that fools never differ? :-P

  4. Bramus! · 3 years ago

    Heck, published the article I had in draft (and now quickly finalized), even though it’s practically the same as it handles the same topic (see my previous comment): http://www.bram.us/2007/06/12/my-dotd-duplicatingcopying-rowsrecords-with-mysql/