MySQL Magic: Find and Replace Data
Recently, I needed to find and replace all instances of “http://website” in the wp_comments table of the WordPress database. Fortunately, SQL provides a simple way to find and replace data with its wonderful UPDATE function.
General Example
Using the SQL UPDATE command is straightforward. Here is the general syntax:
UPDATE table_name SET field_name = replace( field_name, 'string_to_find', 'string_to_replace' ) ;Simply replace the table_name and both instances of field_name with your specific information, and then edit string_to_find, and string_to_replace with the desired values. This is pretty standard stuff, but it is always a good idea to backup your database before executing commands. To run a “Find and Replace” via phpMyAdmin, simply login, select your database, and enter the command via the SQL tab. Copy, paste, and go!
Specific Examples
To use an example from a recent article, let’s find and replace all instances of the string “http://website”. For this example, we want to find all instances located within the wp_comments table of our perishable_wrdp1 database. Specifically, we will query the comment_author_url field of the wp_comments table. Finally, we want to replace all instances of our target string with “http://website.com/”. We use this SQL command:
UPDATE wp_comments SET comment_author_url = replace( comment_author_url, 'http://website', 'http://website.com/' ) ;..and with that, our database is updated in less than one second. All instances of http://website have been replaced with http://website.com/. Easy, right? Sure, now let’s say we would like to replace all instances of the target string with an empty value, effectively removing it altogether. We would use this command:
UPDATE wp_comments SET comment_author_url = replace( comment_author_url, 'http://website', '' ) ;Notice the empty set of single quotes ('') indicating the null replacement value. After executing this command, all instances of http://website have been replaced with nothing, which is the same as simply deleting the target string. “Why not just run a simple DELETE command?” I hear you say. Sure, we could do that as well:
DELETE from wp_comments WHERE comment_author_url = "%http://website%" ;With this, all instances of records associated with (see comment) http://website have been deleted from the comment_author_url field. This command is used frequently in the removal of spam comments from WordPress blogs. WordPress also includes a user-friendly method of mass-deleting or mass-editing comments via its handy “Mass Edit Mode” (WP Admin → Manage tab → Comments tab → Mass Edit Mode link). Simply select any/all comments and click the “Delete Checked Comments” button.
Cheers!
11 responses to “MySQL Magic: Find and Replace Data”
Nice catch,
i may use it someday ;)
thanks
Hmm. Haven’t all records containing that string actually been deleted, in which case you just killed a bunch of valid data? I think you still want to stick with the UPDATE, setting the field to null where that string occurs.
Hmm.. yes, good point. I will update the post to reflect this information. Wouldn’t want anyone to lose any valid comment data on my behalf. — Thanks for the catch!
If Identity Insert is enabled on that table, that approach will only work once.
delete…where id
My < killed the rest.
Delete from wp_ss_stats where id < nwill only delete n records once if id is an identity field.Select id from wp_ss_stats limit 0, nwill return the first n id values.
Delete from wp_ss_stats where id in (Select id from wp_ss_stats limit 0, n)will delete records in the list of the first n id values where the id range can start at any value.As far as WordPress is concerned, the
DELETEcommand works great for things such as quickly eliminating old records from stats plugins. For example, tables for the popular ShortStat plugins will eventually become oversized and bloated with unused data. Using theDELETEcommand, it is easy to erase unwanted, ancient data while leaving the current stats unaffected. Simply use:DELETE FROM `wp_ss_stats` WHERE id < nwhere
nis the number of entries that you would like to delete.Mark,
Your code returns the following error on MySQL 5.0.27-standard:
#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'Looking into it, your code should do the job, but it seems there is a bug in recent versions of MySQL.
Is there another way to write the query?
I suspect you might be able to use limit on the delete:
Delete from wp_ss_stats limit 0, nbut I’d recommend testing it on a copy of a table…
This confirms that:
http://dev.mysql.com/doc/refman/5.0/en/delete.html
And you could do an
order by id ascto make sure you’re deleting the “bottom” n records.Another approach would be to take advantage of the infamous
BETWEENoperator:DELETE FROM `wp_ss_stats` WHERE id BETWEEN n AND mwhere
n,mrepresent the first record and last record, respectively..Very true and fine method. However, if your goal was to periodically purge old data, say 100 records every month, you want to be able to do so regardless of what the IDs are.
What I’m really striving for here is the equivalent of TOP in MSSQL:
Delete top n * from tableI think Mark has the right idea here. A generalized query that is independent of table data (such as
id) enables us to automate the procedure at specified intervals via cron, PHP, etc.Combining and slightly tweaking the two queries from comment #8 gives us this:
DELETE FROM `wp_ss_stats` ORDER BY `id` ASC LIMIT n..which seems to work great at deleting the oldest
nnumber of records from the ShortStat table,wp_ss_stats.