MYSQL create relationships in existing table

Question

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?

Do you mean by concept or would you need help writing the MySQL code? — Christoffer
– Christoffer, Commented May 24, 2022 at 7:03
I suggest you make the question a bit more clear. With a sample of your current data, and a sample of the desired result (I guess the picture you display is the desired result?). And then make it clear that you need help developing the algorithm and the code. Otherwise your question will probably be closed. I'll try to get back and help you later today — Christoffer
– Christoffer, Commented May 24, 2022 at 7:24
create table department auto_increment d#, populate with name from employee, alter table employee add column sub, populate sub from dept based on name, drop name column from employee.. Or were you hoping for something less cumbersome? — P.Salmon
– P.Salmon, Commented May 24, 2022 at 7:29

Akina · Accepted Answer · 2022-05-24 07:48:23Z

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).

Collectives™ on Stack Overflow

MYSQL create relationships in existing table

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related