My DOTD : Duplicating/Copying rows/records with (My)SQL

MySQL Copy RecordsEver ran into a situation where you had to copy quite a few records within the same table? Last week I ran into such a situation for a webapp I’m writing. In the webapp one can manage groups of users. From those users one can create a selection (based on a LIKE search) and then perform an action on that selection: be it move, copy or delete.

From action to SQL statement

The moving of a heap of users can be done easily with a simple UPDATE-query and a delete of course with a simple DELETE-query. But how to copy a bunch of users?

One option is to select all users from the table (based on the criteria) and then (via a serverside script) either build one big INSERT-query with multiple values and perform that query, or perform a large amount (equal to the size of the selection) of INSERT-queries.

When thinking over that idea I immediately threw it off the table as it’s pretty intensive and I was pretty sure MySQL would support some sort of COPY command, to duplicate the rows in one single query.

MySQL.Manual.to.the.rescue!

As the MySQL Manual helps me out quite a lot I started searching there but didn’t find anything when looking for a few simple terms such as “copy”, “duplicate”, etc.. Via Google I finally ended up on a page linking to the INSERT ... SELECT Syntax page in the online MySQL Manual. The page talks about copying records from one table to an other, but one can also use that to duplicate records within the same table.

Query Syntax

The syntax to use this type of query is really easy as it’s a combination of 2 types of queries (hence its name INSERT ... SELECT):

  • Construct your insert as you would normally do:
    [sql]INSERT INTO table (column1, column2, column3) VALUES (value1, value2, value3)[/sql]
  • Replace the part starting from the values with the SELECT you would make to select the rows you want to duplicate:
    [sql]INSERT INTO table (field1, field2, field3) SELECT FROM table (field1, field2, field3)[/sql]

Back to the webapp

Now, in the webapp I had to copy a bunch of users (with a userid, username, useremail, groupid, etc) to a new set of records with a different userid and different groupid.

Leaving out the userid in both the SELECT and INSERT fixes the first issue, as the AUTO_INCREMENT on the userid column will automatically number them.

To fix the second issue a trick has to be applied: “select the new values AS the columname

Finally I ended up with something like this:

[sql]INSERT INTO users (u_id, g_id, u_name, u_email) VALUES
SELECT u_id, ‘6’ AS g_id, u_name, u_email FROM USERS WHERE g_id = 5[/sql]

In laymen’s terms: copy all users from the group with g_id 5 to the group with g_id 6

To come think of it: why hadn’t I discovered this functionality before? I mean, this is like one of the most basic actions one can perform next to insert, delete and update.

Hope you’ve learned something.

B!

Published by Bramus!

Bramus is a frontend web developer from Belgium, working as a Chrome Developer Relations Engineer at Google. From the moment he discovered view-source at the age of 14 (way back in 1997), he fell in love with the web and has been tinkering with it ever since (more …)

Join the Conversation

2 Comments

  1. This query works great, except if I have to re-execute it when there are new entries, it creates duplicates. Do you have a solution, I am a real newbie…

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.