0

I have two custom fields, and both have numeric values:

tnid_01

tnid_01old

Custom Field 'tnid_01' exists for all posts.

Custom Field 'tnid_01old' only exists for some posts.

I am trying to replace the value of 'tnid_01' with the value of 'tnid_01old', but only if 'tnid_01old' exists.

This is what i have so far, but i get a mysql error:

Update wp_postmeta (post_id, meta_key, meta_value)(
SELECT post_id, 'tnid_01', meta_value
FROM wp_postmeta
WHERE meta_key='tnid_01old'
);

thx

  • In the end i used WebElaine's approach with update_post_meta Here is the code i used: $args = array( 'meta_query' => array(array('key' => 'tnid_01old',))); $cpt_query = new WP_Query($args); if ($cpt_query->have_posts()) : while ($cpt_query->have_posts()) : $cpt_query->the_post(); $tnid = get_post_meta($post->ID, 'tnid_01old', true); update_post_meta( $post->ID, 'tnid_01', $tnid ); endwhile; endif; – Philip Jan 6 '18 at 11:23
0

I'm not sure you can do this with MySQL alone, since you'd need to save values and reuse them. Here's a PHP version.

<?php
global $wpdb;
// select all the old postmeta
$oldData = $wpdb->get_results("SELECT * FROM wp_postmeta WHERE meta_key = 'tnid_01old'");
// make sure we have results
if($oldData) {
    // loop through each result
    foreach($oldData as $meta) {
        // save post ID and meta value
        $meta_post_id = $meta->post_id;
        $metavalue = $meta->meta_value;
        // update 'tnid_01' value
        $wpdb->update(
            // table name
            'wp_postmeta',
            // value to set
            array(
                'meta_value' => "$metavalue"
            ),
            // condition to meet: match same post ID but new meta key
            array(
                'post_id' => $meta_post_id,
                'meta_key' => 'tnid_01'
            )
        )
    }
}
?>

If you're on a low-traffic site, you could temporarily paste this into a theme template file and load whatever page uses that template, then make sure to remove this code so it's not hitting the database again. Or a more proper way would be to have it run within wp-admin.

0

The main reason your query is not working is that you have omitted the SET statement.

I'm not sure if you are looking for a one-time solution, or something programmatic you can reuse, but see if this gives you the desired results:

UPDATE wp_postmeta SET 'tnid_01' = 'tnid_01old' WHERE 'tnid_01old' IS NOT NULL

  • this gives me a sql error. could you please be a little more specific with the query? i think its missing something like: UPDATE wp_postmeta SET meta_value = 'tnid_01' = 'tnid_01old' WHERE meta_key LIKE 'tnid_01old' IS NOT NULL – Philip Jan 5 '18 at 15:35

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.