查询更新MYSQL数据库中的数据，在表1中搜索，在表2中更新，如何？

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.

我试图弄清楚如何在一个大型wordpress数据库中快速更改一些东西,通过GUI永远执行,我认为在数据库上执行这个direclty会更有效,因为它是一次性的事情。

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'

我需要执行一个mysql查询,在表wp_termrelationships中查找term_taxonomy_id'8232',如果匹配term_taxonomy_id'8232',则获取该匹配的object_id并使用该数据搜索表wp_posts列ID以进行匹配。当在表wp_posts列ID中找到匹配项时,将每个帖子的post_status更新为“发布”

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

除了表wp_posts中的post_status之外,没有任何更改。

To me this query is very complex, any ideas?

对我来说这个查询非常复杂,有什么想法吗?

1 个解决方案

#1

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

我需要执行一个mysql查询,在表wp_termrelationships中查找term_taxonomy_id'8232'

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

如果匹配term_taxonomy_id'8232',则获取该匹配的object_id并使用它来搜索表wp_posts列ID,以获取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'

当发现这些匹配时,将表wp_posts中每个匹配的帖子的post_status更新为'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.

在这里,我理所当然地认为term_taxonomy_id是一个整数。

#1