2

Great forum! I'm a total newbie to SQL but a quick learner. I have a large database of content, >140k posts. Some posts have Featured Images, others do not.

I would like to edit post_content on posts with Featured Image. I do not need to know anything about the Featured Image, only that the post I edit has one and is not null.

I would basically like to do a simple search and replace in post_content on posts with a Featured Image. I do not want to edit posts that do not have a Featured Image set.

Thank you for any help you can provide.

Improve this question
0

1 Answer 1

Reset to default
-1

I would do this as follows:

  1. Back up the entire DB (or certainly the wp_posts table!)
  2. Run this query to get all posts that have a featured image: SELECT wp_posts.* FROM wp_postmeta INNER JOIN wp_posts ON (wp_postmeta.post_id = wp_posts.ID) AND meta_key='_thumbnail_id';
  3. Export the results to a plain SQL file with SQL inserts
  4. Do your search and replace within that SQL file
  5. Reimport the data.

This assumes your tables have the default prefix obviously.

Improve this answer
3
  • Thanks Jonny. I have a full backup of the database and would like to edit the content directly. Basically I just want to delete an image from the post content that is linked externally. Is this the query to do that: SELECT wp_posts.* FROM wp_postmeta INNER JOIN wp_posts ON (wp_postmeta.post_id = wp_posts.ID) AND meta_key='_thumbnail_id' UPDATE wp_posts SET post_content = REPLACE ( post_content, '<img %>', '' );
    – Riverdog
    Oct 4, 2016 at 23:41
  • Hi. I'm sure it's possible to do it directly in the database, but the fact that you'd need a regular expression because of varying image paths does complicate things, and the general consensus seems to be that MySQL isn't the right tool for this job. Obviously if you want to automate the task, this might not make sense, but if it's just a one-off, you could get the job done in a minute or two by dumping the table and doing a regular expression search and replace in your text editor (e.g. I just did this for Sublime text - replace '<img [^>*]*>' with '')
    – Jonny Perl
    Oct 5, 2016 at 8:47
  • Thanks again for your help, Jonny. I did some research and you're right SQL isn' tthe right tool for the job. I downloaded Expresso and was able to do the search/replace in minutes. Now to upload the edited file :)
    – Riverdog
    Oct 5, 2016 at 21:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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