0

Before the question gets flagged, closed or anything I tried to look for an answer through the site and I always find one part of what I am looking for or what I am trying to do but never something concrete, I've tried to SELECT and UPDATE but never SELECT, UPDATE and REPLACE

So my issue is this:

I have a Wordpress database table wp_posts and another table named wp_postmeta, wp_post have a row named post_content and wp_postmeta have a row called meta_key, I am trying to SELECT, UPDATE and REPLACE a string from wp_post.post_content without altering the other entries. In order to not modify the other entries I need to select from wp_postmeta.meta_key the entries that have the meta_key "cities".

So basically if I run 

SELECT p.post_content FROM wp_post p
INNER JOIN wp_postmeta m ON p.id = m.post_id
WHERE p.post_content LIKE "%TEXT I NEED TO REPLACE%"
AND m.meta_key LIKE "%city%";

That will give me 600 entries from the 5000 that share the same text that I need to replace.

So if I just run

UPDATE wp_post SET post_content = REPLACE 
(post_content, 'TEXT I NEED TO REPLACE', 'NEW TEXT THAT I AM REPLACING');

That will change the 5000 entries and I don't want that, what I need is to replace the 600 entries from the SELECT query I ran before.

In summary I need a way to merge those 2 queries somehow in order to replace the post_content string of those 600 entries without affecting the other 4400 left, it is achievable?

I hope my issue is clear enough. Thanks again.

(I am using Sequel Pro for this)

S. Vinterg
  • 3
  • 1
  • 4
    Possible duplicate of [MySQL - UPDATE query based on SELECT Query](https://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query) – jmoerdyk Jun 27 '18 at 21:04
  • @jmoerdyk - My apologies, but how it is a duplicate regarding to what I am asking? I am not trying to update the whole row or entry, I am just trying to update and replace a string or a fragment from the value of that row. – S. Vinterg Jun 27 '18 at 21:13
  • 1
    Use the replace() function you already know when updating. – The Impaler Jun 27 '18 at 21:17

1 Answers1

1

You can use JOIN and WHERE in an UPDATE query, just like a SELECT query, to filter the rows that get updated.

UPDATE wp_post AS p
JOIN wp_postmeta AS m ON p.id = m.post_id
SET p.post_content = REPLACE(post_content, 'TEXT I NEED TO REPLACE', 'NEW TEXT THAT I AM REPLACING')
WHERE p.post_content LIKE "%TEXT I NEED TO REPLACE%"
AND m.meta_key LIKE "%city%";
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • See also https://stackoverflow.com/a/32082037/495319 for another example of turning an `UPDATE` on more than one table into a `SELECT` (or vice versa). – Wodin Jun 27 '18 at 22:03
  • This is the result I was expecting, thank you so much. I didn't know you could combine it like that. I am new into this. – S. Vinterg Jun 28 '18 at 12:58