New Bookstore! Save 20% on books with discount code: LAUNCH
Web Dev + WordPress + Security

WordPress Tip: Update Email Address in the WordPress Database

Several months ago, I changed my email address to stop spam. Since then, I have been updating every instance of my old address that I can find. In WordPress, I edited all of my theme files and updated my profile information in the “Users” admin area. Several days later while digging through the comments table in the WordPress database, I realized that the user-profile update is only pro-actively effective. There were still hundreds of instances of my old email address associated with comment-author information in the comments table. No big whoop for some, but the devastating inconsistency of it all would have kept me from a good night’s sleep (or maybe that was the caffeine..).

Oooh, shut up and fix it, then

To find my way out of this living nightmare, I began with a little research: a couple of quick queries to determine the total number of

  • old email addresses
  • new email addresses

Made a note of that info, and then executed this magical SQL biscuit:

UPDATE `wp_comments` SET `comment_author_email` = REPLACE( comment_author_email, 'old-email@address.com', 'new-email@address.com' );

Fun stuff. This query essentially replaces all instances of “old-email@address.com” with “new-email@address.com” within the “comment_author_email” field of the “wp_comments” table of the WordPress database. Thus, to drop this steaming little turd for yourself, replace both old and new email addresses with the real dealz. Also, if you are using a custom (i.e., non-default) WordPress table prefix (i.e., something other than “wp_”), you will need to edit “wp_comments” accordingly. As always, it is probably a good idea to backup your database before attempting any modification.

After running this query, all instances of your email address should reflect your new information. To double-check that everything went according to plan, rerun the queries for both old and new email info. If all went according to plan, your old email address should return zero results, and your new email address should return x number of results, where “x” is the sum of old and new email addresses determined above.

Hopefully this exercise will help others clean up old email information. The query can also be used for other “find and replace” operations. By using different query values, you can use this code to find and replace just about anything, anywhere! Amazing.. 

Jeff Starr
About the Author
Jeff Starr = Web Developer. Security Specialist. WordPress Buff.
The Tao of WordPress: Master the art of WordPress.

2 responses to “WordPress Tip: Update Email Address in the WordPress Database”

  1. Thanks so much for this. I knew there had to be a way to match all my comments to my updated email address — it’s always fun to play around with phpMyAdmin! Take that, WordPress!

  2. Jeff Starr
    Jeff Starr 2009/07/19 9:22 pm

    My pleasure, sam — thanks for the feedback :)

Comments are closed for this post. Something to add? Let me know.
Welcome
Perishable Press is operated by Jeff Starr, a professional web developer and book author with two decades of experience. Here you will find posts about web development, WordPress, security, and more »
GA Pro: Add Google Analytics to WordPress like a pro.
Thoughts
Take a screenshot with Firefox (no extension required). Open Developer Tools Settings and enable the “Take a screenshot” button. Then click the button :)
Take a screenshot with Chrome (no extension required). Open DevTools, type Cmd + Shift + P, then type screenshot.
After 10 years working on my 2010 iMac, my upgrade finally arrived. Shiny new iMac shipped from Ireland :)
Too much caffeine weirds me out. But I love the taste of coffee. So once in a while I enjoy a small cup of decaf. Hits the spot.
Chris Coyier is a truly awesome person. One of the finest people I've ever worked with. Just #gottasayit
Excel won't open CSV file because SYLK format? Open it with text editor and add an apostrophe ' at the beginning of the file, save changes, done.
Displaying too many social media buttons and links all over the place imho makes you look desperate and frankly kinda sad.
Newsletter
Get news, updates, deals & tips via email.
Email kept private. Easy unsubscribe anytime.