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 to foo is exactly 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 this makes me cry, I want to split it into two tables like this

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 can I write a script that will create a matching record in foo_new for each existing in foo_old, including new IDs for new associated bar_new records?

Answer 1

First create the foo_new and bar_new tables:

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 with all the distinct bars from the 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 join foo_old with bar_new to fill in 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 will look like after the above 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)