CODEWITHSUNDEEP
mysqldatabasewordpress
karnehe
karnehe

Reputation: 325

Query to update data in MYSQL database, search in table 1 , update in table 2, how?

I am trying to figure out how to quickly change some stuff in a large wordpress database, doing through the GUI takes forever, i think executing this direclty on the database would be much more efficient as it's a one time thing.

I have the follow tables involved:

wp_posts
--------
ID
post_status

wp_termrelationships
--------------------
object_id
term_taxonomy_id

I need to execute a mysql query that looks for term_taxonomy_id '8232' in the table wp_termrelationships , if there's a match for term_taxonomy_id '8232' then take the object_id for that match and use that data to search table wp_posts column ID for a match. When matches are found in table wp_posts column ID update the post_status for each post to 'publish'

No changes is made to anything except the post_status in the table wp_posts.

To me this query is very complex, any ideas?

Upvotes: 0

Views: 69

Answers (1)

Nic3500
Nic3500

Reputation: 8621

Lets deconstruct your requirement, and build the queries:

I need to execute a mysql query that looks for term_taxonomy_id '8232' in the table wp_termrelationships

SELECT object_id
FROM wp_termrelationships
WHERE term_taxonomy_id = 8232


if there's a match for term_taxonomy_id '8232' then take the object_id for that match and use that to search table wp_posts column ID for the data gotten from the match in the object_id

Here I use a sub-query:

SELECT *
FROM wp_posts
WHERE ID IN (SELECT object_id
             FROM wp_termrelationships
             WHERE term_taxonomy_id = 8232)


when those matches are found update the post_status for each matched post in table wp_posts to 'publish'

UPDATE wp_posts
SET post_status = 'publish'
WHERE ID IN (SELECT object_id
             FROM wp_termrelationships
             WHERE term_taxonomy_id = 8232)

Here I take for granted that term_taxonomy_id is an integer.

Upvotes: 2

Related Questions