When working with MySQL databases it’s often necessary to convert a regular field into one that refers to a second table using a foreign key. The process for this requires several steps:

1. Insert distinct column values into new secondary table.
2. Replace (update) existing table fields with proper foreign key for the related record in the secondary table.

This process can be time consuming if attempted manually. Luckily, MySQL 4.1+ allows for subqueries as well as the INSERT… SELECT… syntax.

The first step just requires running that insert select.

INSERT INTO contact_specialties (specialty)
SELECT DISTINCT specialty FROM `contacts` WHERE specialty != '' ORDER BY specialty;

Now that you have the distinct values in the second table, you can run an update query that uses a subquery to identify the proper foreign key for the old value.

UPDATE contacts SET specialty = (
SELECT id FROM contact_specialties WHERE contact_specialties.specialty=contacts.specialty);


This method assumes that you’re moving to a many-to-one relationship. But what if you need to add a third table to transform it further into a many-to-many relationship?

INSERT INTO contact_specialties_link (contact_id, specialty_id)
SELECT contacts.id, contact_specialties.id
FROM contacts
LEFT JOIN contact_specialties ON contact_specialties.specialty = contacts.specialty;

Possibly related posts:

1. MySQL Fulltext Min Word Length