UPDATE and SET sql query through PHP-CodePudding

I am running this query through phpMyAdmin in a Wordpress site:

SELECT trid FROM wp_icl_translations WHERE element_type = 'post_product' AND language_code = 'el'

This query returns a bunch of IDs. I want to collect these inside one variable and then run a foreach to update another table depending on the results I have. I do so like this:

<?php
global $wpdb;
$result = $wpdb->get_results("SELECT trid FROM wp_icl_translations WHERE element_type = 'post_product' AND language_code = 'el'");
foreach($result as $row) {
    // run UPDATE code here
}
?>

Now the things I want to update come from this query:

UPDATE wp_postmeta SET meta_value = replace(meta_value, 'Specs','Specifications') WHERE post_id = $row->trid

It is a simple change of the word Specs to Specifications. But how do I write the above query inside the foreach in my code above? If I do var_dump($row->trid) I get correct results; all the IDs of the posts. So I just want to run the UPDATE query for each ID I find.

CodePudding user response：

There is no point to get the data, then update it row by row. Just do it in one statement:

UPDATE wp_postmeta 
SET meta_value = replace(meta_value, 'Specs','Specifications') 
WHERE post_id IN
(
    SELECT element_id
    FROM wp_icl_translations
    WHERE element_type = 'post_product' 
        AND language_code = 'el'
)