Ever 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
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.
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.
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
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.