The task for today is to clone a record in an SQL table. To say the same thing another way, we want to copy a database record or duplicate a database record. In some cases this is trivially easy, but in other cases it is not.
First of all, let’s identify the record we want to clone:
mysql> select * from settings where hostname="foo"; +-------------+------+----------+ | value | data | hostname | +-------------+------+----------+ | AC3PassThru | 0 | foo | +-------------+------+----------+ 1 row in set (0.00 sec)
OK, there’s nothing remarkable about that. Let’s go ahead and clone it:
mysql> insert into settings select * from settings where hostname="foo"; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from settings where hostname="foo"; +-------------+------+----------+ | value | data | hostname | +-------------+------+----------+ | AC3PassThru | 0 | foo | | AC3PassThru | 0 | foo | +-------------+------+----------+ 2 rows in set (0.00 sec)
Note we must account for the fact that we are allowing non-unique records in this table. Look what happens if we try to duplicate a record that was already non-unique:
mysql> insert into settings select * from settings where hostname="foo"; Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from settings where hostname="foo"; +-------------+------+----------+ | value | data | hostname | +-------------+------+----------+ | AC3PassThru | 0 | foo | | AC3PassThru | 0 | foo | | AC3PassThru | 0 | foo | | AC3PassThru | 0 | foo | +-------------+------+----------+ 4 rows in set (0.00 sec)
You can see that if we start with two records that match the “where” condition, and duplicate each of them, we end up with four records.
The methods of section 1.1 fail if applied to a table that has a unique index.
mysql> insert into channel select * from channel where chanid=21051; ERROR 1062 (23000): Duplicate entry '21051' for key 1
However, this problem is easy to solve. The crucial point is to realize that when we clone something, we don’t expect the offspring to be identical in every way. Identical twins may be genetically identical, but they typically have different locations, different Social Security numbers, et cetera.
The most convenient way to get around the restrictions on unique keys is to use a temporary table. Copy the record of interest to a temporary table, change what needs to be changed, and then copy it back.
CREATE TEMPORARY TABLE chan2 ENGINE=MEMORY SELECT * FROM channel WHERE chanid=21051; UPDATE chan2 SET chanid=21109; ## Change the unique key ## Update anything else that needs to be updated. INSERT INTO channel SELECT * FROM chan2; DROP TABLE chan2;
There are many ways of solving this problem, some of which are less elegant than others.
| Elegant Solution | Less-Elegant Approach |
| The SQL statements in section 1.2 mention only the fields that need to be changed during the cloning process. They do not know about – or care about – other fields. The other fields just go along for the ride, unchanged. This makes the SQL statements easier to write, easier to read, easier to maintain, and more extensible. | Non-experts often suggest using a SELECT statement that explicitly names all the fields that need to be copied verbatim, i.e. all the fields except the ones that need to have unique values. See e.g. reference 1. This is at best laborious and inelegant, and becomes even more laborious if you want to change the table structure and/or re-use the SQL code in connection with other tables. |
| We solved the problem using only ordinary MySQL statements, as given in reference 2. No other tools or programming languages were required. | Non-experts often suggest using some other programming language to write a program that constructs the required SQL statements. (You can see how this temptation might arise in connection with the aforementioned ill-advised effort to name all the fields.) |
| We construct a fully-correct record, then insert it into the main database in one atomic operation. For this reason, using a temporary table as discussed in section 1.2 is often preferable even in situations where the database engine would allow the more direct method mentioned in section 1.1. | It would be bad practice to insert a not-quite-correct record into the main database and then try to fix it in place. |
Note that the “ENGINE=MEMORY” directive is optional. Also note that saying “TYPE=MEMORY” means the same thing, but is passé and deprecated.