CODEWITHSUNDEEP
mysqlsql
Oterox
Oterox

Reputation: 658

mysql update table with values within table

I have a table with this structure

table: WP_ICL_TRANSLATIONS
translation_id | element_type | element_id | trid | language_code | source_language_code
18               post_page      15           14     en              Null
427              post_page      1228         14     zh-hans         en
20               post_page      17           15     en              Null
166              post_page      228          15     zh-hans         en

and another table like this:

table: WP_POSTMETA
ID   | post_content
15     english content... 
1228   chinese content... 
17     english content... 
228    chinese content...

What I need to do is update the field post_content in english with the content in chinese. The posts relation is stored in the first table using the field trid

So, if i want to update the record with id 15 in WP_POSTMETA i have to go to the WP_ICL_TRANSLATIONS table and search for another id with the same trid and then get that id.With that id i can update the WP_POSTMETA table .

What i have now its a select to get the corresponding chinese ID for a english ID:

select element_id from wp_icl_translations where trid=(select trid from wp_icl_translations where element_id=779) and element_id<>779;

but i have to enter the id manually.

Is it possible a query to do that update? maybe using a temporal table?

Upvotes: 1

Views: 291

Answers (1)

rsanchez
rsanchez

Reputation: 14657

UPDATE WP_POSTMETA p1, WP_POSTMETA p2
     , WP_ICL_TRANSLATIONS t1, WP_ICL_TRANSLATIONS t2
   SET p1.post_content = p2.post_content
 WHERE t1.trid = t2.trid
   AND t1.element_id = p1.ID
   AND t2.element_id = p2.ID
   AND t1.language_code = 'en'
   AND t2.language_code = 'zh-hans'

Upvotes: 1

Related Questions