Mass rows copying (duplicating) with filed customization – MySQL

Let’s suppose we have a table with the following structure and data:

# `table`
id | a | b | c |
1 | “aaa” | “xxx” | “ccc”
2 | “aab” | “yyy” | “ccc”
3 | “aba” | “yyy” | “ccc”
4 | “abc” | “xxx” | “ccc”
5 | “dcz” | “xxx” | “eee”

Now, we want to copy some records (only some columns) to the same table AND change some of them with a fixed value.
– duplicate all the rows with `b` equal to “xxx”
– for the new rows inserted, the value of `c` has to be changed to “ddd” (fixed value!)
– `id` is the primary key, auto increment

Query (pay attention to the quote characters):

NULL as `id`,
“ddd” as `c`
FROM `table`
WHERE `b`=”xxx”)


id | a | b | c |
1 | “aaa” | “xxx” | “ccc” #row1
2 | “aab” | “yyy” | “ccc”
3 | “aba” | “yyy” | “ccc”
4 | “abc” | “xxx” | “ccc” #row4
5 | “dcz” | “xxx” | “eee” #row5
6 | “aaa” | “xxx” | “ddd” #copied from #row1
7 | “aab” | “xxx” | “ddd” #copied from #row4
8 | “dcz” | “xxx” | “ddd” #copied from #row5

Note 1:
The insert operation by using a subquery is not an ‘atomic’ operation for the recent versions of MySQL. If there are table constraints and a new record is not accepted (e.g: duplicate record for a key defined on columns), only that record won’t inserted (not all of them!).
Example: If before there was a record with the same values as in line 9 and there was a unique key on columns (a,b,c), the query would insert only rows on line 6,7 and 8 (9 fails).
Some old version of MySQL stop execution in case of duplicate entries. In this case, add IGNORE to the query to skip duplicates: INSERT IGNORE INTO
Note 2:
If you add a new column to the table, the query will fail !!
Tested on MySQL 5.1.36

2 thoughts on “Mass rows copying (duplicating) with filed customization – MySQL

  1. MySql is the powerful database as well as provide the enhance security here are provide some code of the Database..

Comments are closed.