MySQL: Moving Columns Of Data Between Two Tables
Say you had a table called books with data such as:
+----+----------------------------+-----------+
| pk | title | author_id |
+----+----------------------------+-----------+
| 1 | The Lost Symbol | 1 |
| 2 | Follow Us Home | 2 |
| 3 | The Man in the High Castle | 3 |
+----+----------------------------+-----------+
Another another table, authors:
+----+----------------+
| pk | name |
+----+----------------+
| 1 | Dan Brown |
| 2 | Mark Edwards |
| 3 | Philip K. Dick |
+----+----------------+
And for some reason, you do not see the point of having a separate authors table (although you probably would as it is not a 1 to 1 relationship, but in this simplified example, lets say it is), and so you need to transfer all those names into the column of the books table.
Well first, make sure the columns in both tables are of the same type. In this example, MySQL would easily transform that int field into varchar.
UPDATE
books b
SET
b.author_id = (
SELECT a.name
FROM author a
WHERE a.pk = b.author_id
)
The inner SELECT gets all the values that you want and returns a single column of results. The SET is able to take this result set and apply it to the column of every record. If you have more results in authors than you do in books, it will pull NULL values in.
If you get an error code 1175 (running in safe update mode), simply add
WHERE b.pk > -1
to the UPDATE to make MySQL happy and tell it to update every single record.
Finally, you might want to change the name of the original column in book, as it is no longer the id (you could do this first to change the type):
ALTER TABLE books CHANGE COLUMN `author_id` `author_name` VAR_CHAR(255) NOT NULL;
A very simple example of a very simple task.