CODEWITHSUNDEEP
phpmysqlsqlwordpress
kauffmanes
kauffmanes

Reputation: 518

How to update the categories of existing custom post types?

I am picking up work on a legacy tool and am redoing a page that works with custom post types. The new post type we'll call new_resource, and has the same "category" identifiers as the existing legacy resource type, old_resource. I'm pretty new to Wordpress, so there may have been a better way of doing that than just starting from scratch, but this is where I'm at. My goal is to use the same data that the existing resources used, so I don't have to create all of the new posts.

I ran two queries that updated the existing resource types to the new one:

update wp_posts set post_type = 'new_resource' where post_type = 'old_resource';

and

update wp_posts set guid = replace(guid, 'old-resource', 'new-resource');

All of that is working.

What I want to do now is update the custom taxonomy (I'm not using the default categories built in). We'll call it resource-category for the example. The existing old-resource posts all had their resource-category assigned, and when I query wp_terms, I can see all of the categories. I've set up those SAME categories in the new-resource type. Is there a query that I can run that will select all of the resources with a type of new-resources (all of them are now, after I ran that update query above), and set their categories? Like update their term_id where the term_id is still pointing to an ID of the old category type?

Wordpress Version: 4.2.2 PHP Version: 5.3.3

Edit:

I ran this query:

mysql> select term_id, name, slug from wp_terms where slug = 'videos';

+---------+--------+--------+
| term_id | name   | slug   |
+---------+--------+--------+
|      69 | Videos | videos |
|     177 | Videos | videos |
+---------+--------+--------+
2 rows in set (0.00 sec)

The term_id 69 belongs to the old resource type. The term_id 177 belongs to the new resource. I want all of my current resources (the ones I already ran the update resource type query on) to point to term_id 177 instead of 69, but I'm not sure of the relationship between posts and terms.

Something like update the term_id for all new-resources, set 69 to 177.

Upvotes: 1

Views: 2050

Answers (2)

kauffmanes
kauffmanes

Reputation: 518

I figured it out. I ran a query that selected all of the info I'm comparing:

SELECT p.post_title as “wp_post_title”, tr.term_taxonomy_id as “term_rship_id”, tt.taxonomy as “term_tax_name”, tm.slug as “terms_slug”
FROM wp_posts p
INNER JOIN wp_term_relationships tr ON p.ID = tr.object_id
INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
INNER JOIN wp_terms tm ON tm.term_id = tt.term_id
WHERE tt.taxonomy = 'exp_wp_resource_category' or tt.taxonomy = 'resource-categories';

Then I updated my existing taxonomy_ids in the wp_term_relationship table.

UPDATE wp_term_relationships SET term_taxonomy_id = 176 WHERE term_taxonomy_id = 13;

13 is the ID of the old-resource video type, and 175 is the ID of the new-resource video type.

Just took some digging through the Wordpress table relationships.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

The update set only the column in set clause .. and i set clause ypu can have more than a column

You could do the two update using an update only 

 update wp_posts 
         set post_type = 'new_resource', 
         guid = replace(guid, 'old-resource', 'new-resource')
 where post_type = 'old_resource';

Upvotes: 1

Related Questions