MySQL: Copying Column Data Between Tables

Everyone who administrates databases, large or small, will eventually encounter the need to copy data from one table to another. This often occurs in application development when tweaking data model designs, copying data stored in an existing table into a newly-created table. In such cases it is often also necessary to update the existing table with the row ID of the associated data in the new table. Consider the following example:

Joe maintains a database for customer information. His database includes a table for his customers’ personal information (named Personal) as well as a table for their business information (named Business). Both tables include many similar columns (street_address, city, state, zip), and Joe realizes that it is better to contain this info in a new table called Addresses. He decides to create a new column in both the Personal and Business tables which will store the ID of the corresponding row in the new Addresses table.

Now, Joe has to figure out how to copy the data from the existing tables into the new table. Being an application developer used to control flow structures, he conceives a loop that parses each row, copies the data from A to B, then updates the old row with the last insert ID. He sets out to find the syntax to perform loops in MySQL, but he comes away confused and dejected. That’s because although MySQL provides loop functionality through cursors, Joe has learned that cursors are inefficient, difficult to implement, and inflexible. Joe decides that there must be a better way to move his data.

Joe calls his database admin friend, Pete, and explains what he wants to do. After spending five minutes nitpicking the semantics of Joe’s explanation, Pete offers a clever solution:

“Just copy the existing row ID into a throwaway column in the new table, then perform a multi-table update to update the new ID field in the old table. Pfft…developers.”

A good developer, fluent in the best practices of procedural programming, would consider this approach to be a hack. But in the world of relational databases, it’s how to get things done. Here’s the code:

# create a new temporary column to store the existing row ID
ALTER TABLE Addresses ADD old_id INT(11);

# copy the data
INSERT INTO Addresses (street_address, city, state, zip)
SELECT street_address, city, state, zip FROM Personal;

# match the rows using the ID stored in the temporary column
# then update the new_id field in the old table
UPDATE Addresses, Personal
SET Personal.new_id = Addresses.id
WHERE Personal.id = Addresses.old_id;

# delete the temporary column
ALTER TABLE Addresses DROP old_id;

To copy rows from both existing tables at once, add a UNION clause:

# copy the data
INSERT INTO Addresses (street_address, city, state, zip)
SELECT street_address, city, state, zip FROM Personal;
UNION
SELECT street_address, city, state, zip FROM Business;

Obviously, these queries may be adapted to include transformations, joins, or any other necessary operations.

So, the big lesson that we learned along with Joe today is that when application developers get their hands dirty in the database, it often requires a retooling of the way we think, not just a syntax reference.

1 Comment

1 Trackback

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>