For each loop in MySqL

Question

I have a mySQL table with 5 columns.
* translation_id is an auto increment primary key
* element_id is id of the post/article
* language_code is the language of the post/article
* trid is the id of the original post/article of which the element is a translation
* source_language_code is the language of the original post/article

+----------------+------------+---------------+------+----------------------+  
| translation_id | element_id | language_code | trid | source_language_code |  
+----------------+------------+---------------+------+----------------------+  
| 1              | 1          | hu            | 1    | hu                   |  
| 2              | 2          | hu            | 2    | hu                   |  
| 3              | 3          | hu            | 3    | hu                   |  
| 4              | 4          | hu            | 4    | hu                   |  
| 5              | 5          | en            | 1    | hu                   |  
| 6              | 99         | en            | 2    | hu                   |  
| 7              | 27         | en            | 3    | hu                   |  
| 8              | 8          | en            | 4    | hu                   |  
| 9              | 9          | es            | 1    | hu                   |  
| 10             | 10         | es            | 2    | hu                   |  
| 11             | 11         | es            | 3    | hu                   |  
| 12             | 12         | es            | 4    | hu                   |  
| 13             | 13         | nl            | 1    | hu                   |  
| 14             | 14         | nl            | 2    | hu                   |  
| 15             | 55         | nl            | 3    | hu                   |  
| 16             | 16         | nl            | 4    | hu                   |  
| 17             | 77         | fr            | 1    | hu                   |  
| 18             | 18         | fr            | 2    | hu                   |  
| 19             | 19         | fr            | 3    | hu                   |  
| 20             | 20         | fr            | 4    | hu                   |   
+----------------+------------+---------------+------+----------------------+

I the above table, you can see that the Hungarian (hu) pages with IDs 1,2,3,4 have been translated into English, Spanish, Dutch and French.

What I would like to do is change the original/source language to English. One part is easily achieved: set source_language_code to en for all.

But that does only part of the job. The second bit is to set trid as 5 if it is 1, 99 if it is 2, 27 if it is 3 and 8 if it is 4.

In other words, for each Hungarian post, I need to find
the element_id of the English post whose trid matches the Hungarian post,
and then set that English post's element_id as the trid of all posts (in all languages) whose trid matches the Hungarian post.

FOR EACH `element_id` AS hungarian FROM `table` WHERE `language_code` = "hu" {  
    SELECT `element_id` AS english FROM `table` WHERE `trid` = hungarian AND `language_code` = "en";
    UPDATE `table` SET `trid` = english WHERE `trid` = hungarian;
}

Target output

+----------------+------------+---------------+------+----------------------+  
| translation_id | element_id | language_code | trid | source_language_code |  
+----------------+------------+---------------+------+----------------------+  
| 1              | 1          | hu            | 5    | en                   |  
| 2              | 2          | hu            | 99   | en                   |  
| 3              | 3          | hu            | 27   | en                   |  
| 4              | 4          | hu            | 8    | en                   |  
| 5              | 5          | en            | 5    | en                   |  
| 6              | 99         | en            | 99   | en                   |  
| 7              | 27         | en            | 27   | en                   |  
| 8              | 8          | en            | 8    | en                   |  
| 9              | 9          | es            | 5    | en                   |  
| 10             | 10         | es            | 99   | en                   |  
| 11             | 11         | es            | 27   | en                   |  
| 12             | 12         | es            | 8    | en                   |  
| 13             | 13         | nl            | 5    | en                   |  
| 14             | 14         | nl            | 99   | en                   |  
| 15             | 55         | nl            | 27   | en                   |  
| 16             | 16         | nl            | 8    | en                   |  
| 17             | 77         | fr            | 5    | en                   |  
| 18             | 18         | fr            | 99   | en                   |  
| 19             | 19         | fr            | 27   | en                   |  
| 20             | 20         | fr            | 8    | en                   |   
+----------------+------------+---------------+------+----------------------+

Please add the expected result. My first impression is that the query update my_table set source_language_code = 'en', trid = trid + 4 will do the job, but I may be wrong. — The Impaler
– The Impaler, Commented May 21, 2020 at 19:36
@the-impaler Thanks! But the pattern of element ID's is just an artifact of the illustration and doesn't hold in actual data. — Kris
– Kris, Commented May 22, 2020 at 3:00

Barmar · Accepted Answer · 2020-05-26 15:33:30Z

1

Use UPDATE with a self-JOIN:

UPDATE yourTable AS t1
JOIN yourTable AS t2 ON t1.trid = t2.trid
SET t1.trid = t2.element_id, t1.source_language_code = 'en'
WHERE t2.language_code = 'en';

DEMO

Since you want to change all rows, you don't need to test language_code = 'hu'.

edited May 26, 2020 at 15:33

answered May 21, 2020 at 19:47

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Kris Over a year ago

Thank you kindly. This does the job for the rows with language_code hu but not for other rows.

Barmar Over a year ago

I thought those were the only ones you wanted to update.

Barmar Over a year ago

You said "for each Hungarian post".

The Impaler Over a year ago

@OC2PS It seems your sample data is not representative. Please improve it.

Kris Over a year ago

@Barmar Apologies. I have added expected output to the question

Collectives™ on Stack Overflow

For each loop in MySqL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related