MySQL: splits row into multiple rows with join table?

Question

I have an existing table with the following structure

+-------------+-------+-------+-------+
| employee_id | val_1 | val_2 | val_3 | ...
+-------------+-------+-------+-------+
|         123 |     A |     B |     C |

I want to change this single table into 2 tables - one which contains the values in seperate rows, and another with becomes a join table for this. For example, the above would be turned into this:

+-------------+--------+      +----+-------+
| employee_id | val_id |      | id | value |
+-------------+--------+      +----+-------+
|         123 |      1 |      |  1 |     A |
+-------------+--------+      +----+-------+
|         123 |      2 |      |  2 |     B |
+-------------+--------+      +----+-------+
|         123 |      3 |      |  3 |     C |
+-------------+--------+      +----+-------+

What's the best SQL to use to convert the existing table into these 2 new tables? I can create the values table easy enough, but I'm not sure how to create the join table at the same times.

Sorry not proficient in MySQL but any answer should factor in that your original table may have a uniqueness constraint on employee_id (eg if it's the primary key) that will cause problems in your new table format... constraint needs to be removed, relaxed/extended (eg composite PK on employee_id and val_id), or it might just be easier to create a _new_ employee table (it would certainly be advisable to copy rather than replace, for testing of whatever answer is accepted, anyhow). — Sepster, Dec 24 '12 at 07:30
You want to run an insert from a select for each column of the original table. I'd post a full answer but can't be bothered testing for accuracy right now. — mikebabcock, Dec 24 '12 at 07:32
Hi Sepster - The 2 tables are both new, so they shouldn't have to worry about the existing table's constraints? — John Farrelly, Dec 24 '12 at 07:33
Hi mikebabcock, that's what I'm doing to create the new values table, I'm just not sure how to populate the join table as I do this... — John Farrelly, Dec 24 '12 at 07:34
@JohnFarrelly yes you're right, sorry - I misunderstood that you were creating new tables. PS use @ in front of a username to ensure they receive notification that you've addressed them in a comment — Sepster, Dec 24 '12 at 07:34
Here's how to retrieve the identity of a column just inserted: http://stackoverflow.com/questions/897356/php-mysql-insert-row-then-get-id — Sepster, Dec 24 '12 at 07:35
Thanks @Sepster, I guess I'm wondering how to do the individual inserts in a loop to be able to use the last id. I'm doing an INSERT INTO val_table SELECT val_a from existing_table - so the bulk insert doesn't let me insert into the join table as I go (or at least I don't know how to). — John Farrelly, Dec 24 '12 at 07:39

Sepster · Answer 1 · 2012-12-24T07:48:33.700

1

Something like this (psuedo-code only, sorry):

For each row in (SELECT employee_id, val_1, val_2, val_3 FROM existing_table)
{
   for each val in (row.Values)
   {
     INSERT INTO new_values (val)
     val_id = SELECT LAST_INSERT_ID();
     INSERT INTO new_employees (employee_id, val_id);
  }
}

There's probably a set-based way of doing this to avoid the loops... but sorry, I don't know what it is as like you, I'm not sure how to get the identity of the values table back into the parent employee table.

And also, while cursors are generally frowned on, this sort of one-off operation is exactly what they're designed for (ie I wouldn't recommend cursors for regular transaction or report processing, but for a re-structure of data.... why not?).

edited Dec 24 '12 at 07:48

answered Dec 24 '12 at 07:41

Sepster

4,800
20
38

Yep, that's what I want to do, I'm just not sure of the MySQL syntax :) For example, I'm wondering if I have to create a procedure to put the loop in, or can MySQL handle adhoc loop statements? – John Farrelly Dec 24 '12 at 07:44
@JohnFarrelly http://stackoverflow.com/questions/5817395/how-can-i-loop-through-all-rows-of-a-table-mysql – Sepster Dec 24 '12 at 07:45

Akhil · Answer 2 · 2012-12-24T08:41:30.450

0

for the first result

`INSERT INTO new_val
SELECT  emp_id, REPLACE(UPPER(column_name), 'VAL_', '') FROM   
information_schema.COLUMNS ,
employee
WHERE TABLE_NAME = 'employee' AND TABLE_SCHEMA = 'myschema' AND column_name LIKE 'VAL_%'`;

using first result, populate the query and use it to insert into new table; May be minor fine tuning required. Not tested
SELECT CONCAT('select ', new_val.number, ', VAL_', new_val.number, ' FROM employee, new_val WHERE new_val.emp_id = employee.emp_id and new_val.number = ', val.number, ' union all' ) FROM val ;

edited Dec 24 '12 at 08:41

answered Dec 24 '12 at 07:40

Akhil

2,602
23
36

I am new to stackoverflow and bad in formatting. kindly excuse – Akhil Dec 24 '12 at 08:39

score 0 · Accepted Answer · answered Dec 24 '12 at 09:14

So here's what I ended up writing to do this. As the procedure name suggests, I was expecting there to be a more straightforward way of doing this!

CREATE PROCEDURE iWasHopingItWouldBeSimpler()
BEGIN
   DECLARE loop_done BOOLEAN DEFAULT 0;

   DECLARE emp_id BIGINT(20);
   DECLARE val1 DECIMAL(19,2);
   DECLARE val2 DECIMAL(19,2);
   DECLARE val3 DECIMAL(19,2);

   DECLARE emp CURSOR
   FOR
   SELECT employee_id, val1, val2, val3 FROM existing;   

   -- Declare continue handler
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET loop_done=1;

   OPEN emp;

   -- Loop through all rows
   REPEAT
      FETCH emp INTO emp_id, val1, val2, val3;

      INSERT INTO new_values (value) VALUES(val1);
      INSERT INTO new_join (employee_id, values_id) VALUES(emp_id, LAST_INSERT_ID());

      INSERT INTO new_values (value) VALUES(val2);
      INSERT INTO new_join (employee_id, values_id) VALUES(emp_id, LAST_INSERT_ID());

      INSERT INTO new_values (value) VALUES(val3);
      INSERT INTO new_join (employee_id, values_id) VALUES(emp_id, LAST_INSERT_ID());

   -- End of loop
   UNTIL loop_done END REPEAT;

   CLOSE emp;
   SET loop_done=0;
END;

MySQL: splits row into multiple rows with join table?

3 Answers3