Can I move a column from one mysql table to another, and replace the original column with the new id's form the new table?

Question

My project is growing and need to expand from a single table to two.

I have posts, and each post has an author. Currently the author is just a column in posts, but I have made an new table called authors. I would like to populate the new table with all unique instances of authors and replace the author values in the posts table with the new author ids.

Is there some sql magic that can help me do this? My post table is 30k entries so I would like to avoid doing it manually ;)

thanks!

Answer 1

1. Create the new table Authors.

   CREATE TABLE Authors (
     author_id INT AUTO_INCREMENT PRIMARY KEY,
     author_name VARCHAR(20)
   );
   

2. Populate Authors with the set of distinct authors from Posts.

   INSERT INTO Authors (author_name) 
   SELECT DISTINCT author_name FROM Posts;
   

3. Add an author_id column to Posts, which references Authors.

   ALTER TABLE Posts ADD COLUMN author_id INT, 
       ADD FOREIGN KEY (author_id) REFERENCES Authors(author_id);
   

4. Update Posts with the corresponding author_id values, based on an (inefficient) join based on the author_name in each table. MySQL supports multi-table UPDATE syntax, which is not standard SQL, but it's very handy for these situations.

   UPDATE Posts JOIN Authors USING (author_name)
   SET Posts.author_id = Authors.author_id;
   

5. Drop the now-redundant author_name from Posts, and optionally make author_id not nullable.

   ALTER TABLE Posts DROP COLUMN author_name, MODIFY author_id INT NOT NULL;