[FIXED] SQL query to add category to bunch of posts where post_content matches keyword

Issue

I'm trying to figure out how to properly go about adding a category to a massive number of posts in a wordpress site, i prefer doing this directly in the database as it's faster and a good learning experience :)

Here's my mysql query so far:

SELECT *
FROM wpsite_posts
WHERE post_content like '%keyword%'

This lists all the posts i want to modify, but the trouble is the column for category is not in the same table as wpsite_posts

The category is in the table called wpsite_term_relationships

In the table wpsite_posts there's a colum called ID which is the data that has to match with column OBJECT_ID in the table wpsite_term_relationships

In table wpsite_term_relationships there's a column called term_taxonomy_id this column contains the category and tag id's for wordpress.

What i am trying to do is with one sql query, search table wpsite_posts column post_content find the 'keyword' in the post_content and then use the matching ID from post_content to look inside table wpsite_term_relationships for matches in the column object_id and when there's a match i want to add a new entry to column term_taxonomy_id with '5232'

5232 = the new category id i wish to have these posts inside.

Sorry for the long read, i have no idea how to better explain this, any mysql gurus able to take a crack at this?

Solution

You can use a combination of an INSERT INTO and a SELECT.

The first line of this query describes the insert and the next three lines describe a list of rows that you want to insert.

The number of fields in your SELECT statement need to match the number of fields in the INSERT. Values in quotes will be literal (so the '5232' will be inserted into every row as the term_taxonomy_id), while values that match column names in the table in the FROM clause will be inserted based on the values returned from the SELECT.

INSERT INTO wpsite_term_relationships (term_taxonomy_id, object_id)
SELECT '5232',  ID
FROM  `wpsite_posts` 
WHERE  `post_content` LIKE  '%keyword%'

Alternatively

(This probably won't do it for OP, but might help someone else in a similar situation)

If there are less than 1000 posts with the keyword, you could do it within Wordpress. Just go to the 'Posts' page, set the page to list 1000 entries, search for your keyword and add the categories using a bulk edit.

This might be too resource intensive for some servers and could cause a timeout.

Answered By - vlasits