use mysql to replace one custom field value with another custom field value

Question

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

Answer 1

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.

Answer 2

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