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)