How do you move custom fields to custom taxonomies using WP Queries

Question

To save complication I have imported a custom fields (categories & terms) to the wp_postmeta table, which are linked to respective posts. Shown as listed in wp_postmeta:

meta_id post_id meta_key                meta_value
183     45      job_listing_region      Kingston
208     46      job_listing_region      Fajardo
233     47      job_listing_region      Letchworth
258     48      job_listing_region      Oxford
283     49      job_listing_region      Beaulieu-sur-Mer

Using a SQL query how do you move these to taxonomies & terms to the following tables?

wp_term_relationships
wp_term_taxonomys
wp_terms

I'm finding the table relationship a little confusing, whether to JOIN or UPDATE in this instance. A good reference article below for someone who knows what they're doing. Thanks in advance, Murray

SQL to update custom post taxonomies

Answer 1

I would recommend using WordPress function calls to set up your terms, instead of SQL. This is assuming you've already registered a custom taxonomy called job_listing_region.

// Add this to your theme's functions.php
function convert_meta_to_term() {
  global $wpdb;
  $meta_query = $wpdb->get_results("
    SELECT *
    FROM $wpdb->postmeta
    WHERE meta_key = 'job_listing_region'
  ");
  foreach ($meta_query as $meta) {
    // Append new terms for each post
    wp_set_object_terms($meta->post_id, $meta->meta_value, 'job_listing_region', true);
  }
}
add_action('wp_ajax_convert_meta_to_term', 'convert_meta_to_term');

Now you can call this function by going to the URL http://your-blog.tld/wp-admin/admin-ajax.php?action=convert_meta_to_term.