Quick Answer
INSERT INTO `new table` (`column z`) VALUES ( SELECT `column a` FROM `old table` )
Detailed Answer
Ok, so for those of you looking for a little more information about how/why the above MySQL query works, let’s get into the details. Firstly you likely want to move data from one column in one table in a MySQL database and move this data to another column in another table as part of a refactoring exercise on your database, data and database schema. This is never a simple process and this blog post isn’t going to go into all the considerations you’ll need to make as part of this process, so bear that in mind, instead we’re simply going to look into how to do this basic part of the process and what the details mean.
Prepare Your New Table and Column
Get prepared to store your data in a new location by making sure your database table has a new column of the exact same data type as the column you are moving the data from. Any discrepancies here will cause issues, you need to prepare in much more detail if you are migrating data types, which is beyond the scope of this blog post.
Migrate Your Data
Now you’ve got your new table and column prepared, it’s time to copy the data from the old table to the new table by running the MySQL command outlined at the start of this blog post. Let’s get into the details of what this means though;
INSERT INTO `new table` (`column z`)
The above part of the MySQL query is a basic INSERT statement, specifically inserting data into a specific column. Then the next part of the query is telling MySQL what data to insert into this column.
VALUES ( SELECT `column a` FROM `old table` )
Since the sub-select query returns multiple records, and due to the fact that this sub-select is part of an INSERT statement, MySQL is smart enough to realise that you want to copy the data across.
Now the above isn’t going to work in every scenario and to be fair it really only works perfectly when you don’t have to take into account NOT NULL fields and non-AUTO_INCREMENT fields. Things get significantly more complex when you start to add in these types of…. Complexities, but hey.
Hopefully this is a good starting point to get you thinking along the right lines for migrating data from one table and column in a MySQL database and over to another new table and column with ease as part of your database and data migration processes. Leave a comment with your nuances that you’ve had to deal with along the way – it’s great to share!
Michael Cropper