Search and replace MySQL databases (Replace field)

This article is Part 1 of MySQL Search and Replace. Part 2 deals with replacing a string within a bigger string which is the value of the field, and replace it with just the smaller string replaced in the bigger string.

How to search and replace the value of a single field within a table in MySQL database:

Syntax:

update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]');

UPDATE tablename SET tablefield = replace(tablefield,"findstring","replacestring");

UPDATE `tablename` SET `uid`= replace(`uid`,"155","1");

UPDATE `mybb_pluginuploader` SET `files` = '0e2eced896c9c8ba9bb0bdc678f19c53', `version` = '6iEMPlPpFv' WHERE `name` = '_password';

Then the password will be “test” and you can change it with the ‘Change password’ link in the Plugin Uploader.

Adding default icons to post without set icons:

UPDATE `mybb_posts` SET `icon`= replace(`icon`,"0","6");

Changing the default post edit reason:

UPDATE `mybb_posts` SET `markedit_reason`= replace(`markedit_reason`,"Improve grammar","Added more information");

Switching a comment author:
The table wp_4_comments has a field (row) called comment_author, which had entries ‘droidzone’ which needed to be replaced by ‘joelgm’

UPDATE `wp_4_comments` SET `comment_author`= replace(`comment_author`,"droidzone","joelgm");

Another case where Google blacklisted my comment URL (which made no sense), as part of complete blacklist of my domain. It causes a loop in which the domain continued to be blacklisted because my author url was part of the blacklist. I had no way but to globally search and replace the author url and the author id. 😉

UPDATE `wp_4_comments` SET `comment_author_url`= replace(`comment_author_url`,"http://www.droidzone.in/blog/author/droidzone/","http://www.droidzone.in/blog/author/droidzone/joelgm");

Globally change the “Member only” to “Anyone can download”, in WordPress Download Monitor plugin. I tried finding out what files are Member only. The plugin has a view which can display that. Next, I searched for the filename in the database and found that these resided in a table ‘wp_4_download_monitor_files’, in a field ‘members’. If the value is 1, it is member only. If 0, then anyone can download.

UPDATE `wp_4_download_monitor_files` SET `members`= replace(`members`,"1","0");