In MySQL, how can I split one existing table into many new related tables?

Question

I have an existing table that resembles this. The number of bars for foo is actually two in reality

table foo_old ID int name string bar_name_1 string bar_code_1 int bar_name_2 string bar_code_2 int 

And since it makes me cry, I want to split it into two tables, such as

 table foo_new ID int name string ID_bar_1 int ID_bar_2 int table bar_new ID int name string code int 

My question is: how to write a script that will create a corresponding entry in foo_new for each of the existing ones in foo_old, including new identifiers for the new related bar_new entries?

Answer 1

First create the foo_new and bar_new :

 CREATE TABLE foo_new ( id int NOT NULL AUTO_INCREMENT PRIMARY KEY, name varchar(100), id_bar_1 int, id_bar_2 int ); CREATE TABLE bar_new ( id int NOT NULL AUTO_INCREMENT PRIMARY KEY, name varchar(100), code int, UNIQUE (code) ); 

Then fill the bar_new table bar_new all the individual columns from foo_old :

 INSERT INTO bar_new (name, code) SELECT DISTINCT bar_name_1 name, bar_code_1 code FROM foo_old UNION SELECT DISTINCT bar_name_2 name, bar_code_2 code FROM foo_old; 

Then attach foo_old to bar_new to populate foo_new :

 INSERT INTO `foo_new` (name, id_bar_1, id_bar_2) SELECT f.name, b1.id, b2.id FROM foo_old f JOIN bar_new b1 ON (b1.code = f.bar_code_1) JOIN bar_new b2 ON (b2.code = f.bar_code_2); 

Test case:

 CREATE TABLE foo_old ( id int NOT NULL PRIMARY KEY, name varchar(100), bar_name_1 varchar(100), bar_code_1 int, bar_name_2 varchar(100), bar_code_2 int ); INSERT INTO foo_old VALUES (1, 'foo1', 'bar1', 1, 'bar2', 2); INSERT INTO foo_old VALUES (2, 'foo2', 'bar6', 6, 'bar5', 5); INSERT INTO foo_old VALUES (3, 'foo3', 'bar4', 4, 'bar3', 3); INSERT INTO foo_old VALUES (4, 'foo4', 'bar2', 2, 'bar7', 7); INSERT INTO foo_old VALUES (5, 'foo5', 'bar6', 6, 'bar5', 5); INSERT INTO foo_old VALUES (6, 'foo6', 'bar4', 4, 'bar1', 1); INSERT INTO foo_old VALUES (7, 'foo7', 'bar7', 7, 'bar4', 4); INSERT INTO foo_old VALUES (8, 'foo8', 'bar3', 3, 'bar8', 8); 

This is how foo_new and bar_new after the specified operation:

 SELECT * FROM foo_new ORDER BY name; +----+------+----------+----------+ | id | name | id_bar_1 | id_bar_2 | +----+------+----------+----------+ | 3 | foo1 | 1 | 4 | | 6 | foo2 | 2 | 7 | | 5 | foo3 | 3 | 6 | | 4 | foo4 | 4 | 5 | | 7 | foo5 | 2 | 7 | | 1 | foo6 | 3 | 1 | | 2 | foo7 | 5 | 3 | | 8 | foo8 | 6 | 8 | +----+------+----------+----------+ 8 rows in set (0.00 sec) SELECT * FROM bar_new ORDER BY name; +----+--------+------+ | id | name | code | +----+--------+------+ | 1 | bar1 | 1 | | 4 | bar2 | 2 | | 6 | bar3 | 3 | | 3 | bar4 | 4 | | 7 | bar5 | 5 | | 2 | bar6 | 6 | | 5 | bar7 | 7 | | 8 | bar8 | 8 | +----+--------+------+ 8 rows in set (0.00 sec)