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:
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");