Useful WordPress SQL Queries

Update wordpress site address:

UPDATE wp_options
SET option_value = replace(option_value, ‘http://www.oldsite.com’, ‘http://www.newsite.com’)
WHERE option_name = ‘home’ OR option_name = ‘siteurl’;

Replace the table fields value with new phrase (ex: update urls from old site to new site):

update wp_posts set post_content=REPLACE(post_content,’http://www.olddomain.com’,'http://www.newdomain.com’)

Delete all article/page revision and the tags:
If you revised your wordpress post 3 times and it will insert 3 lines to your database thus 3 times bigger than original size, it’s helpful to delete those unused content.

DELETE FROM wp_posts WHERE post_type = "revision";

Reset admin user password:

UPDATE wp_users SET user_pass = MD5( ‘new_password’ ) WHERE user_login = ‘admin’;

Reset Admin username:

UPDATE wp_users SET user_login = ‘newname’ WHERE user_login = ‘admin’;

Export all email address from comments:

SELECT DISTINCT comment_author_email FROM wp_comments;

Delete all spam comments:

DELETE FROM wp_comments WHERE comment_approved = ‘spam’;

Delete all comments containning links:

DELETE FROM wp_comments WHERE comment_content LIKE "%<a href=%" AND comment_type = ”;

Share

- has written 121 posts.


Leave a Reply

You must be logged in to post a comment.