SQL query to set posts in bulk based on the post content

Question

I have thousands of offline links in my posts, and I'd like to set them all to draft. I know the links are in the format example.com/fileX. So I needed a SQL query to search the post content and whatever post that contain that url will be set to draft.

I think this might be a good start:

UPDATE tb_posts 
SET post_status = 'draft' 
WHERE 

But I don't know to do the search inside the WHERE clause. I intend to use ARI Adminer Plugin to edit the database.

Answer 1

Look into MySQL Wildcards.

The correct MySQL Query for your search should be

WHERE tb_posts.post_content LIKE "%example.com%"

You should also be sure that you only target published posts (in contrast to nav_menu_items, revisions etc). Expand your WHERE with this:

AND tb_posts.post_type IN ('post','page',*All Posttypes you use additionally*) AND tb_posts.post_status = 'publish'

So your complete Query should look like this (assuming that you only use posts and pages):

UPDATE tb_posts 
SET post_status = 'draft' 
WHERE post_content LIKE "%example.com%"
AND post_type IN ('post','page')
AND post_status = 'publish'

Happy Coding!