MySQL – How to Duplicate Each Row of a Table

MySQL

I've got a table of Product records from an old db that I must change to import on a new php system. They have the form:

name, property1_IT, property1_EN, property1_FR

I need to transform them by duplicating each row and change it like:

name, language, property1

where property1 will have the related translated value.

In other words, I'll need one row for each product translation, thus adding a language column and deleting the property translation fields.

Is it possible with sql or should I just use php to cicle through the records and doing the duplication tasks?

Best Answer

You can do it like this:

create new_table as
select name, 'italian' as language, property1_IT as property1 from old_table
union all
select name, 'english' as language, property1_EN as property1 from old_table
union all
select name, 'french' as language, property1_FR as property1 from old_table;
Related Question