Latest TweetsDifference between mod_alias and mod_rewrite perishablepress.com/difference…
Perishable Press

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!

Jeff Starr
About the Author Jeff Starr = Web Developer. Security Specialist. WordPress Buff.
Archives
11 responses
  1. Jack Book July 25, 2007 @ 9:40 pm

    Nice catch,
    i may use it someday ;)
    thanks

  2. With this, all instances of http://website have been deleted from the comment_author_url field.

    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.

  3. Jeff Starr

    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!

  4. August Klotz August 22, 2007 @ 11:29 am

    As far as WordPress is concerned, the DELETE command 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 the DELETE command, it is easy to erase unwanted, ancient data while leaving the current stats unaffected. Simply use:

    DELETE FROM `wp_ss_stats` WHERE id <n

    where n is the number of entries that you would like to delete.

  5. If Identity Insert is enabled on that table, that approach will only work once.

    delete…where id

  6. My < killed the rest.

    Delete from wp_ss_stats where id &lt; n will only delete n records once if id is an identity field.

    Select id from wp_ss_stats limit 0, n

    will 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.

  7. Jeff Starr

    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?

  8. I suspect you might be able to use limit on the delete:

    Delete from wp_ss_stats limit 0, n

    but 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 asc to make sure you’re deleting the “bottom” n records.

  9. August Klotz August 22, 2007 @ 5:18 pm

    Another approach would be to take advantage of the infamous BETWEEN operator:

    DELETE FROM `wp_ss_stats` WHERE id BETWEEN n AND m

    where n, m represent the first record and last record, respectively..

  10. 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 table

  11. Jeff Starr

    I 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 n number of records from the ShortStat table, wp_ss_stats.

[ Comments are closed for this post ]