Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

Migrating question:

How would I structure a MySQL query to move the value from one meta_key to another meta_key?

eg: I have an old meta_key of "guest_sort" associated with "posts" to "tf_exhibitor_sort" under the post type "exhibitor_listing"

I've already moved the posts from the original post category over to the post_type. Still possible, or am I outta luck and will have to do some data-entry?

Thanks.

share|improve this question
    
how are the old 'posts' associated w/ the new 'exhibitor_listing' post type? this'd be easy if you were just changing posts to posts, but i'm not sure how to handle the changing post type –  helgatheviking Feb 27 '12 at 3:02
    
Well they all still have their original post-IDs, they just have a different post_type now. –  Nadine Feb 27 '12 at 4:23
add comment

3 Answers 3

I hope I understand the question correctly . When you move a post , the meta keys and values associated with it should remain with the post itself, like all post meta (including the attachments , custom-image etc..) - so there should be no problem to keep the values. Normally ,The only thing that SHOULD happen is a change the 'post_type' property of the post.

but anyhow, if your question is about RENAMING meta keys , then this should dod the trick :

update wp_postmeta 
set meta_key = 'new_key_name' 
where meta_key = 'old_key_name' 

Of course, to update VALUES you can use the same approach

UPDATE `wp_postmeta` SET `meta_value` = replace(meta_value, 'old_value', 'new_value') WHERE `meta_key` LIKE 'your_key'

EDIT 1 : i forgot to mention : ** BACKUP YOUR DATABASE BEFORE ANY TRIAL **

EDIT 2 : following comment : To copy from one field to another (I got a bit confused with your naming and which value you want where , so I post the generic sentence.

UPDATE `TABLE` SET `FIELD2` = `FIELD1'
share|improve this answer
    
sorry...premature posting...had to edit. That helps me understand some things better. Thanks. You are right, the old values did stay with the posts, at least it looks like it in the database. I'll try the renaming thing, see if it works in this case. –  Nadine Feb 27 '12 at 4:13
    
Jeez, this boards 5 minute rule on comment editing gets annoying. :) Anyways, as a side note: What I'm trying to write is (i think): In all posts within the post_type exhibitor_listing replace the value of tf_exhibitor_sort with the value of guest_sort. –  Nadine Feb 27 '12 at 4:23
    
@krembo99 +1 on DB backup. I've copied/pasted SQL statements in days long past and messed up production envs. Your first statement should work great. –  Brian Fegter Feb 27 '12 at 4:29
    
Oh man I know, re: data backup. I have totally borked instals in my day. Now I always keep backups. Thanks for the comments guys! Appreciated. –  Nadine Feb 27 '12 at 18:34
add comment

defo back up your data!! it might not be the most efficient, but you could also do this w/ WP functions instead of SQL queries if you are more comfortable speaking WP (like i am)

run this one time:

/*
 * Converts Old Content 
 */
function kia_convert_content(){
$products = get_posts(array('numberposts'=>-1,'post_type'=>'exhibitor_listing'));

foreach( $products as $post ) : setup_postdata($post); 

    // get old meta
    $test = get_post_meta($post->ID,'guest_sort', true);

    // update new meta
    update_post_meta($post->ID,'tf_exhibitor_sort',$test);

    // delete old meta
        delete_post_meta($post->ID, 'guest_sort');

    endforeach;

}

you can just delete it after you reload your theme once (and add that function to an init hook or something). or you could be super cool and use this 'run once' code i found from Bainternet.

/*
* run Once class
* http://en.bainternet.info/2011/wordpress-run-once-only
*/
if (!class_exists('run_once')){
    class run_once{
        function run($key){
            $test_case = get_option('run_once');
            if (isset($test_case[$key]) && $test_case[$key]){
                return false;
            }else{
                $test_case[$key] = true;
                update_option('run_once',$test_case);
                return true;
            }
        }

        function clear($key){
            $test_case = get_option('run_once');
            if (isset($test_case[$key])){
                unset($test_case[$key]);
            }
        }
    }
}




/*
 * convert the content exactly 1 time
 */

$run_once = new run_once;
if ($run_once->run('kia_convert_content')){
    add_action('init','kia_convert_content');
}
share|improve this answer
    
Hmm, i couldn't get this to work for me. I threw it in an add_action('init','kia_convert_content'); in the functions.php file and did a reboot, but no dice. Maybe I'm not putting it in the right place. –  Nadine Feb 27 '12 at 19:37
    
bummer. i can't really test it since i don't have your data, but i did just use this recently. was your post_meta an array? (i assumed it wasn't.. hence the true in the get_post_meta). all i can suggest would be to double check the post_type and post_meta values. hopefully you are having better luck w/ the SQL queries. –  helgatheviking Feb 27 '12 at 20:37
add comment
up vote -3 down vote accepted

So, friend came up with a script for this. You put it in your root folder of your instal and it flips the values from one meta key to the other. I thought I'd share it in case anyone else need it.

 <?php
// Functions 
function db_connect($host,$db,$user,$pass){
  if(@mysql_connect($host, $user, $pass)){
    @mysql_select_db($db)or
    $content = sql_debug();
  }
  else {
    $content = sql_debug();
  }
  return @$content;
}

// Variables to change
$sql_username = "username";
$sql_password = "password";
$sql_host = "database host";
$sql_db = "database name";

// What is your post type that you want to limit this change to?
// IE: Only posts from 'exhibitors' will have this change
$post_type = 'post_type';

// What is the meta source? In this example it is website_link
// this is the "old" value you want to copy to the new value
$old_value = 'old_key';

// What is the meta destination? In this example lets use tf_ex_site_url
// BOTH key names must already exists in this instance of the script. If
// both don't exist connected with the post_id, the value wont be copied.
$new_value = 'new_key';

// Ok Go
db_connect($sql_host,$sql_db,$sql_username,$sql_password);
$counter = 0;
$query = "SELECT * from wp_posts WHERE post_type = '$post_type'";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
  $post_id = $row['ID'];
  $query2 = "SELECT * from wp_postmeta WHERE post_id = '$post_id'";
  $result2 = mysql_query($query2);
  while ($row2 = mysql_fetch_array($result2)) {
    // Do update here
    if ($row2['meta_key'] == $old_value) {
      $counter++;
      $temp_new = $row2['meta_value'];
      $query_update = "UPDATE wp_postmeta SET meta_value = '$temp_new' WHERE meta_key = '$new_value' AND post_id = '$post_id'";
      $result_update = mysql_query($query_update);
      // Un-comment the below line if you want to BLANK the old value (ie in this case make website_link = to nothing after
      // updating the value of tf_exhibitor_sort
      // mysql_query("UPDATE wp_postmeta SET meta_value = '' WHERE meta_key = '$old_value' AND post_id = '$post_id'");
    }
  }
}

echo $counter." records updated.";

?>
share|improve this answer
    
This is a bad practice as it assumed the table prefix to be wp_. Also you should be using WP functionality to properly escape data and prepare your queries. Always use the $wpdb object for queries within WordPress. Why not create a script to be run within WP rather than use an external file to do this? –  Brian Fegter Mar 1 '12 at 4:22
    
This is running on my local. Its not being uploaded to a server, ever. Also I had a friend write it for me as a quick solution I could use locally and then remove it. Since its local, I know what my own table pre-fixes are in this case. i wouldn't use this live, but as a local at home solution it saved me a lot of hours. –  Nadine Mar 6 '12 at 3:51
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.