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' 

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

  • 1
    What do you mean by the format "example.com/fileX"? Do you mean the Links are always starting with "example.com/file" followed by a number, a string of characters? Please elaborate... Jul 4 '18 at 6:42
  • It doesn't matter if it has a string. If the search contains example.com will be enough to set it to draft! Note that I'll not change anything on the post content, only to the post_status. Jul 4 '18 at 7:27

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!

  • A genius is a genius. Thank you! Worked perfectly. Jul 10 '18 at 19:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.