0

I am new to mysql and phpmyadmin so excuse my question if it's basic. I need a query to replace the 15$ to 10$ on all posts and pages post_title, post_excerpt and post_content using the insert method not update method. also is there any way to undo the query if things went wrong ?

Noha el-Deeb
  • 11
  • 3
  • Is this the same question as https://stackoverflow.com/questions/74137932/combine-3-queries-in-one-query-to-replace-all-strings-15-with-10 ? – Isaac Bennetch Oct 22 '22 at 02:02

4 Answers4

0

You should back up your database first from PHPMyAdmin -> Export.

You can't REPLACE without UPDATE.

As a second solution, after backup, if your MySQL dump is small enough, you could edit the SQL file with Notepad++, replace 15$ with 10$ and import everything in a new database and switch DB connections. This would meet the requirement: of using only the INSERT method.

Valeriu Ciuca
  • 2,084
  • 11
  • 14
  • I need to updated all at once post_title, post_content and post_excerpt in single posts and pages all in one query to avoid several updates for posts and pages as it will cause many google indexing. so I eneded up here but I need it all to be combined in one query, what do you think ? UPDATE wp_posts SET post_content = replace(post_content, '15$', '10$'); UPDATE wp_posts SET post_title = replace(post_title, '15$', '10$'); UPDATE wp_posts SET post_content = replace(post_excerpt, '15$', '10$'); – Noha el-Deeb Oct 20 '22 at 09:43
  • Yes, the UPDATE with REPLACE it's the fastest method, but like I mentioned, please backup your database first. You don't have to worry about google indexing, googlebot will re-crawl important pages at least every few days, and even more frequently in some cases: https://www.searchenginejournal.com/googles-john-mueller-reveals-often-site-re-indexed-search/241427/ – Valeriu Ciuca Oct 20 '22 at 10:32
0

I'm not sure what your database looks like without an example and I'm not sure why you want to avoid the update method but maybe this will help you with your question.

This does use the update function however but I don't see a way around it other than exporting your database to an sql file, updating the rows through either a script or search and replace in a text editor.

My solution would be to use your text editor or use a query like

UPDATE posts SET post_title = REPLACE(post_title,'$15','$10');

You might also want to take a look at this: How to search and replace all instances of a string within a database?

Bowiemtl
  • 82
  • 8
  • I am not sure why our head of development was shocked that I used the update method and insisted that we have to use the insert method ;/ she didn't answer any further questions. – Noha el-Deeb Oct 20 '22 at 09:43
  • so I eneded up here but I need it all to be combined in one query, what do you think ? UPDATE wp_posts SET post_content = replace(post_content, '15$', '10$'); UPDATE wp_posts SET post_title = replace(post_title, '15$', '10$'); UPDATE wp_posts SET post_content = replace(post_excerpt, '15$', '10$'); – Noha el-Deeb Oct 20 '22 at 09:44
  • I don't know how your senior developer (I'm assuming) is expecting to run this process through. I don't know if you're operating with a live database nor how large it is but I would personally suggest shutting it down for maintenance, (carefully) text editing the sql file, upload it back and leave it at that. It could be smart to have a test database for this while you're at it. – Bowiemtl Oct 20 '22 at 10:37
  • Otherwise if you really need to make it into one query you'll have to write it all out by hand with the previously mentioned query: `UPDATE table SET column_name = REPLACE(column_name, 'oldstring', 'newstring') WHERE column_name LIKE '%oldstring%'` – Bowiemtl Oct 20 '22 at 10:38
  • **EDIT**: `UPDATE table SET column_name = REPLACE(column_name, 'oldstring', 'newstring'), column_name2 = REPLACE(column_name2, 'oldstring2', 'newstring2')` You could also try adding `WHERE column_name LIKE '%oldstring%'` which may or may not optimize the query – Bowiemtl Oct 20 '22 at 10:44
  • it's huge database that's why i wont be able to update file. UPDATE table SET post_title = REPLACE(post_title, '15$', '10$'), post_content = REPLACE(post_content, '15$', '10$'), post_excerpt = REPLACE(post_excerpt, '15$', '10$') – Noha el-Deeb Oct 20 '22 at 15:18
0
UPDATE wp_posts SET post_title = REPLACE WHERE post_type = 'post',post_type = 'page' (post_title, '$15', '10$'), post_content = REPLACE WHERE post_type = 'post',post_type = 'page'(post_content, '$15', '$10'), post_excerpt = REPLACE WHERE post_type = 'post',post_type = 'page'(post_excerpt, '$15', '$10');
Noha el-Deeb
  • 11
  • 3
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 26 '22 at 08:51
0

let me introduce my solution : replace specific string in posts

UPDATE wp_posts SET post_title = REPLACE(post_title, '$15', '$10'), post_content = REPLACE(post_content, '$15', '$10'), post_excerpt = REPLACE(post_excerpt, '$15', '$10') WHERE post_type = 'post';

but I would recommend using the first answer for more covering

UPDATE wp_posts SET post_title = REPLACE WHERE post_type = 'post',post_type = 'page' (post_title, '$15', '10$'), post_content = REPLACE WHERE post_type = 'post',post_type = 'page'(post_content, '$15', '$10'), post_excerpt = REPLACE WHERE post_type = 'post',post_type = 'page'(post_excerpt, '$15', '$10');

replace specific string in pages

UPDATE postmeta SET meta_value = REPLACE(meta_value, '$15', '$10');

change on wp yoast SEO plugin values meta_titles and meta_description

UPDATE wp_yoast_indexable SET title = REPLACE(title, '$15', '$10'), description = REPLACE(description, '$15', '$10');
Noha el-Deeb
  • 11
  • 3