mysql update table with values within table

Question

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?

rsanchez · Accepted Answer · 2013-06-07 09:11:49Z

1

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'

answered Jun 7, 2013 at 9:11

rsanchez

14.7k1 gold badge40 silver badges54 bronze badges

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

Collectives™ on Stack Overflow

mysql update table with values within table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related