Below is a query I put together. It is super complicated, and someone likely will come along and replace it with a one liner, but it I believe does the trick based on the test case I put together based off your sample data.
WARNING: BACK UP YOUR DATA BEFORE YOU ATTEMPT TO DO ANYTHING WITH THIS.
Assumptions:
- Based on my understanding, post_content will be an entire post, not just a link. So we need to find the links within a chunk of text, and just replace the link portion leaving the rest of the text untouched.
- Links are in the form of text.
- The link text does not contain the greater-than or less-than characters (< or >). I.e. something like “This is < link text!” would break this.
- If there is more than one link in a single post_content that you want to replace, you’ll have to run the update more than once to catch the 2nd+ link.
Below is a SELECT of the sample data I used, the UPDATE I ran, and then another SELECT showing the resulting data. Note the “goodlink.com” link that is unaffected since it does not match the domain we are replacing.
Update query:
update wp_posts set post_content = replace(post_content, left(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content)), locate('</a>', mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content))) + 3), left(mid(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content)), locate('>', mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content))) + 1, length(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content)))), locate('<', mid(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content)), locate('>', mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content))) + 1, length(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content))))) - 1)) where mid(post_content, locate('http://', post_content), length(post_content)) LIKE 'http://dontneedlink.com%';
mysql> select * from link_test;
+---------------------------------------------------------------------------+
| post_content |
+---------------------------------------------------------------------------+
| Text1 <a href="http://dontneedlink.com">Test</a> Text2 |
| Text 3 <a href="http://dontneedlink.com">Test Again</a> Text4 |
| Text 5 <a href="http://goodlink.com">Keep Me</a> Text 6 |
| Text 7 <a href="http://dontneedlink.com/childpage">Test Again 2</a>Text 8 |
+---------------------------------------------------------------------------+
4 rows in set (0.00 sec)
mysql> update link_test set post_content = replace(post_content, left(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content)), locate('</a>', mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content))) + 3), left(mid(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content)), locate('>', mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content))) + 1, length(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content)))), locate('<', mid(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content)), locate('>', mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content))) + 1, length(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content))))) - 1)) where mid(post_content, locate('http://', post_content), length(post_content)) LIKE 'http://dontneedlink.com%';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from link_test;
+---------------------------------------------------------+
| post_content |
+---------------------------------------------------------+
| Text1 Test Text2 |
| Text 3 Test Again Text4 |
| Text 5 <a href="http://goodlink.com">Keep Me</a> Text 6 |
| Text 7 Test Again 2Text 8 |
+---------------------------------------------------------+
4 rows in set (0.00 sec)
mysql>
To do the replace for a different domain, just do a find/replace for “dontneedlink.com” and replace it with another domain in the same format.
I tested a few edge cases, but I expect you will run into exceptions. So again, make sure you back up your data.
Once you have a chance to test it let me know how it goes and I’ll try to help more if necessary. =)