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
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?
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;
A a compendium to @tombom’s answer, I’d like to add the fact that if the table has a lot of fields (as in my case) and field order is not a problem, another approach can be to simply add translations after all the other fields, and then delete the old translation ones. So, first:
CREATE TABLE `V36_test` AS SELECT *, 'IT' AS LANGUAGE, XLS_TIPO_IMPUGNATURA_IT AS XLS_TIPO_IMPUGNATURA, XLS_ROTAZIONE_IT AS XLS_ROTAZIONE, [etc...] FROM OLD_TABLE UNION ALL SELECT *, 'EN' AS LANGUAGE, XLS_TIPO_IMPUGNATURA_EN as XLS_TIPO_IMPUGNATURA, XLS_ROTAZIONE_EN as XLS_ROTAZIONE, [etc...] FROM OLD_TABLE [etc.. other languages...]
ALTER TABLE V36_test DROP COLUMN XLS_TIPO_IMPUGNATURA_IT; ALTER TABLE V36_test DROP COLUMN XLS_ROTAZIONE_IT; [etc...] ALTER TABLE V36_test DROP COLUMN XLS_TIPO_IMPUGNATURA_EN; ALTER TABLE V36_test DROP COLUMN XLS_ROTAZIONE_EN; [etc...]
With a smart, multi-line code editor like Sublime Text, it’s very easy and fast to write a query like this even with a lot more fields.