0

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?

2

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;
  • mmm interesting... – Luca Reghellin Oct 15 at 8:19
  • Can use *, [...other fields] to avoid manually writing all non-translation fields (that in my case are a lot) – Luca Reghellin Oct 15 at 13:50
0

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...]

Then:

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.