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 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!
>> why hadn’t I discovered this functionality before?
Because as the manual says…
(This was not possible in some older versions of MySQL.)
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…