如何将MySQL SELECT语句转换为UPDATE语句（WordPress场景）

Got it, let's break this down and turn that SELECT query into a working UPDATE for your WordPress database.

First, let's recap what your original SELECT does: it extracts everything in post_content before the <!--more--> tag (including trimming off the tag itself and any content after it). To turn this into an UPDATE, we just need to take that substring calculation and use it to overwrite the post_content field for matching posts.

Step 1: Verify Your SELECT Results First #

Before running any UPDATE, always confirm the SELECT returns exactly what you want to keep. Run your original query again (with an alias for clarity) to double-check the truncated content is correct:

SELECT post_content, 
       SUBSTR(post_content, 1, LENGTH(post_content) - LENGTH(SUBSTRING_INDEX(post_content,'<!--more-->',-1)) - 1) AS truncated_content
FROM `wp_posts` 
WHERE post_status='publish' AND post_type='post'

This lets you directly compare the original content against what you'll be updating to.

Step 2: The UPDATE Query #

Here's the converted UPDATE statement. It uses the exact same substring logic from your SELECT to replace post_content:

UPDATE `wp_posts`
SET post_content = SUBSTR(post_content, 1, LENGTH(post_content) - LENGTH(SUBSTRING_INDEX(post_content, '<!--more-->', -1)) - 1)
WHERE post_status = 'publish' 
  AND post_type = 'post'
  AND post_content LIKE '%<!--more-->%' -- Only update posts that actually have the more tag

Key Notes to Avoid Mistakes #

• Backup First: Always back up your wp_posts table (or entire database) before running UPDATE queries. If something goes wrong, you can roll back easily.
• Test with LIMIT: To avoid accidentally modifying all posts at once, add a LIMIT clause to test first. For example:

  UPDATE `wp_posts`
  SET post_content = SUBSTR(post_content, 1, LENGTH(post_content) - LENGTH(SUBSTRING_INDEX(post_content, '<!--more-->', -1)) - 1)
  WHERE post_status = 'publish' 
    AND post_type = 'post'
    AND post_content LIKE '%<!--more-->%'
  LIMIT 5; -- Only update 5 posts to verify results
  

• Adjust for Table Prefix: If your WordPress installation uses a custom table prefix (not wp_), replace wp_posts with your actual table name (e.g., myblog_posts).
• Skip Posts Without the Tag: The post_content LIKE '%<!--more-->%' condition ensures we only touch posts that have the tag. Without this, posts without the tag would get their content set to an empty string (since SUBSTRING_INDEX would return the full content, making the length calculation zero).

内容的提问来源于stack exchange，提问作者Terdor Accordet