• Home
  • Sql
  • Search and replace MySQL databases (Replace field)

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");
Β To change a field named uid in Table tablename, do this:
UPDATE `tablename` SET `uid`= replace(`uid`,"155","1");
In MyBB, these are the tables which need to be searched and replaced to fix a wrong uid issue:
posts
threads
I needed to do this because because of merging my admin account with another account, I lost Super admin permissions in MyBB. This was the fix, changing the newuid to uid 1 of Superadmin
To reset the MyBB Plugin uploader password:
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");

 

Joel G Mathew

Joel G Mathew, known in tech circles by the pseudonym Droidzone, is an Android and Linux enthusiast.

His favorite pastime is grappling with GNU compilers, discovering newer Linux secrets, writing scripts, hacking roms, and programs (nothing illegal), reading, blogging. and testing out the latest gadgets.

When away from the tech world, Joel is a practising ENT Surgeon.

2 Replies to “Search and replace MySQL databases (Replace field)”

Comments are closed.