MySQL find and update based on results from 2 tables

Question

First off, please note that this is not answered in the question here: MySQL update table based on another tables value or indeed any other Stack question or answer that I could find!

I converted a forum on a website and now just need to repair the internal URL links on a few hundred of the posts.

Posts are found in the column post_content within the table xyz_posts

ID     post_content  

1467  This is great https://example.com/index.php?topic=1234 I really like it
1468  Hello world
1469  Take a look https://example.com/index.php?topic=5678.0

You can see the URLs are mostly buried in the post text. Note that the example above can sometimes be written with topic=1234.0 at the end, although it is actually stored in the database as a value of 1234. I don't want to rewrite the url and accidentally keep the .0

Here's an example of how I need that table to look:

ID     post_content  

1467  This is great https://example.com/finished-page/ I really like it
1468  Hello world
1469  Take a look https://example.com/another-page/

So, the table xyz_converter maps the old topic number to the new post ID like this:

meta_key             meta_value     value_id  

_bbp_old_topic_id    1234           15675
_bbp_old_reply_id    1234           17439

Caveat here, the number 1234 also exists in this table for forum replies which we don't want, but the meta_key and value_id are different for those as shown. This SQL query works to get to the right one:

SELECT * FROM `xyz_converter` WHERE meta_key LIKE '_bbp_old_topic_id' AND `meta_value` LIKE '1234'

Also in table xyz_posts we map the value_id mentioned above to the post's URL suffix like this:

ID        post_name

15675     finished-page 
15676     another-page 

How do I construct a SQL query that will detect the meta_value in one table and then replace it with the correct final URL as mentioned?

PROCESS SUMMARY

1. Detect the URL in xyz_posts post_content
2. Extract the topic number from the URL (eg 1234). If it's 1234.0 then take just 1234
3. Convert it to a post number in found in xyz_converter value_id (eg 15675) ensuring it is found alongside _bbp_old_topic_id
4. Take the suffix of the URL in xyz_posts post_name (eg 'finished-page')
5. Rewrite the original URL to include the suffix (not forgetting the trailing slashes).

I'm using MySQL 5.7.29 and PHPMyAdmin. I'm fairly new to SQL queries and a Regex noob, but willing to learn more!

Answer 1

A solution that uses only MySQL commands and works in MySQL 5.7 could be like this.

If .0 is present only in the urls you could use a query like this to remove all occurences of .0 from post_content in xyz_posts.

UPDATE `xyz_posts` SET post_content=REPLACE(post_content,'.0 ',' ') WHERE post_content LIKE '%topic=%.0 %';

Then you can use

CREATE TABLE temp_tbl
SELECT CONCAT('index.php?topic=',c.meta_value) as `find_value` ,p.post_name as `replace_value`
FROM `xyz_converter` c
INNER JOIN `xyz_posts` p ON c.value_id=p.id AND `meta_key`='_bbp_old_topic_id'
ORDER BY meta_value DESC;

UPDATE `xyz_posts` p 
INNER JOIN  `temp_tbl` t ON p.post_content LIKE CONCAT('%',t.find_value,'%')
SET p.`post_content`=REPLACE(p.`post_content`,t.find_value,t.replace_value);

The first command will create a temporary table where the first column will be the value that you want to find and replace like index.php?topic=1234 and the second column will be the value you want to be replace with like finished-page

The second command will replace posts_content in xyz_posts taking the first column from temp_tbl and replacing it with the second column.

Below an sql fiddle where you can see the solution in action

http://sqlfiddle.com/#!9/7bce8c

Of course you should first create a copy of your database and try these commands to ensure everything works fine before trying it in your production database.