How to Bulk Update post titles WordPress through SQL

As a WordPress developer or blogger, you often need to go into the database to perform specific tasks that can’t be efficiently handled through the WordPress admin interface or API. One such task could be manipulating or querying posts based on their titles, such as finding or updating all posts with titles that contain the year “2023”. This article will guide you through writing SQL queries for these purposes, ensuring you can handle such tasks with ease and precision.

Searching for Posts with replacing words in the Title

Imagine you need to list all posts from your WordPress site with “2023” in their title. This requirement is typical for content audits, SEO reviews, or bulk editing tasks. Here’s how you can achieve this with an SQL query:

SELECT *
FROM wp_posts
WHERE post_title LIKE '%2023%'
AND post_type = 'post'
AND post_status = 'publish';


Breaking Down the Query

1. Selecting Data: SELECT * tells the database to retrieve all columns from the matching rows. You can replace * with specific column names like ID and post_title if you only need certain data.
2. Targeting the Right Table: FROM wp_posts specifies that the query is to be run on the wp_posts table, where WordPress stores all post-related data.
3. Filtering by Title: WHERE post_title LIKE '%2023%' is the crux of the query. The LIKE operator is used for pattern matching, and %2023% is a wildcard pattern that matches any title containing “2023”.
4. Ensuring Correct Post Type: AND post_type = 'post' ensures that only standard posts are considered, excluding pages or custom post types.
5. Status Check: AND post_status = 'publish' limits the results to published posts. You can modify this to include drafts or other statuses.

Updating Titles Containing “2023”

Now, let’s say you want to update the titles of posts containing “2023”. Perhaps you need to replace “2023” with a new year or modify these titles in some other way.

UPDATE wp_posts
SET post_title = REPLACE(post_title, '2023', 'New Value')
WHERE post_title LIKE '%2023%'
AND post_type = 'post';


Understanding the Query

1. Updating Data: UPDATE wp_posts indicates that you are going to modify data in the wp_posts table.
2. Setting New Values: SET post_title = REPLACE(post_title, '2023', 'New Value') changes the part of the title containing “2023” to “New Value”. You can customize ‘New Value’ as needed.
3. Identifying the Right Posts: The WHERE clause is similar to the search query, ensuring that only posts with “2023” in their titles are updated.

Best Practices and Considerations

• Backup First: Always backup your database before running direct SQL queries. This step is crucial for safety.
• Table Prefix: Replace wp_ with your actual table prefix if your WordPress installation uses a different one.
• Testing: Test your queries on a staging environment before running them on a live site.
• Permissions: Ensure you have the necessary permissions to execute SQL queries on your WordPress database.

Conclusion

Mastering SQL queries for specific tasks like searching for or updating post titles can significantly enhance your capabilities as a WordPress developer. By understanding how to construct and execute these queries, you can handle data-related tasks more efficiently and with greater control. Remember to follow best practices, especially regarding backups and testing, to ensure the integrity and safety of your WordPress sites.