MYSQL create relationships in existing table-CodePudding

I have already a big table with many columns. Now I try to split the table in separate smaller tables and create some relationships. As an example: In my case the department name is directly written in the employee table. Now I want to split them and create the relationship (Sub to D#)

How can I separate them? and delete the department name out of the Employee table?

CodePudding user response：

An example:

CREATE TABLE sub (id INT AUTO_INCREMENT PRIMARY KEY)
SELECT DISTINCT val2
FROM src;

ALTER TABLE src 
    ADD COLUMN sub_id INT,
    ADD FOREIGN KEY (sub_id) REFERENCES sub (id);

UPDATE src
JOIN sub USING (val2)
    SET src.sub_id = sub.id;

ALTER TABLE src
    DROP COLUMN val2;

db<>fiddle here (with some explanations).