Moving data from one table to two new tables

[TeNDoLLA]

Hello,

I need help moving data between the old db structure and the new db structure.

 

 

Old db structure (1 table):

table synonyms
----------------
synonym_id (auto_increment)
item_id
item_name
note_fin
note_eng
note_swe

 

New db structure (2 tables, 3 tables if we count the language table also, which defines the lang_id):

table synonyms
----------------
synonym_id (auto_increment)
item_id
item_name


table synonym_notes
----------------------
synonym_note_id (auto_increment)
synonym_id
note
lang_id

 

The problem is how to move with SQL commands the old data from one old table to the 2 new tables. I should get item_id and item_name from the old table to the new synonyms table. I should also move the corresponding notes to the new synonym_notes table for the 3 languages in a way that the synonym_id in the synonym_notes table refers to the synonym_id in the new synonyms table for the  corresponding items. lang_id's in the new structure are like: fin=1, eng=3, swe=4. Anyone can help me with this transition?

[TeNDoLLA]

I guess this could have been done easiest with some sort of bubblegum loop in PHP, but I wanted to try and do it in SQL. I guess I was thinking too complicated and tried to make it in loop inside a stored procedure. Problem was solved with just a simple joins, probably not the best way but did what I wanted it to do.

 

/* Query to get all synonyms from the old table to the new synonyms table */
INSERT INTO new_synonyms (item_id, item_name)
SELECT item_id, item_name FROM old_synonyms;


/* Below is queries to get all the note translations from the old synonym table
   to the new synonym note translations table. */
   
/* for finnish notes */
INSERT INTO new_synonym_notes (synonym_id, lang_id, note) 
SELECT t2.synonym_id, 1, s.synonym_note_fin FROM new_synonyms t2
JOIN old_synonyms s ON s.item_name = t2.item_name;

/* for english notes */
INSERT INTO new_synonym_notes (synonym_id, lang_id, note) 
SELECT t2.synonym_id, 3, s.synonym_note_eng FROM new_synonyms t2
JOIN old_synonyms s ON s.item_name = t2.item_name;

/* for swedish notes */
INSERT INTO new_synonym_notes (synonym_id, lang_id, note) 
SELECT t2.synonym_id, 4, s.synonym_note_swe FROM new_synonyms t2
JOIN old_synonyms s ON s.item_name = t2.item_name;