Several useful SQL queries for your Wordpress blog.

Sometimes all you need is a SQL querie to master your Wordpress powered blog / website. Use those queries at your own risk.

UPDATE wp_posts SET post_content = REPLACE (post_content, 'src="https://www.old-url.com', 'src="https://the-cdn.new-url.com');

Do this too:

UPDATE wp_posts SET  guid = REPLACE (guid, 'http://www.old-url.com', 'http://the-cdn.new-url.com') WHERE post_type = 'attachment';

Updating user’s password:

UPDATE wp_users SET user_pass = MD5( 'new_password' ) WHERE user_login = 'the-username';

Assign all content from author A to author B

UPDATE wp_posts SET post_author = 'new-author-ID' WHERE post_author = 'old-author-ID';

Deleting versions / revisions of your blog posts:

DELETE a,b,c FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision'

Retrieving comments author emails:

SELECT DISTINCT comment_author_email FROM wp_comments;

Deleting all pingbacks:

DELETE FROM wp_comments WHERE comment_type = 'pingback';

Deleting all spam:

DELETE FROM wp_comments WHERE comment_approved = 'spam';

How to deactivate comments on old posts?

UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2012-01-01'

Problems with character encoding?

Instead of cleaning each comments and blog posts, try this:

UPDATE wp_posts SET post_content = REPLACE(post_content, '“', '“');
UPDATE wp_posts SET post_content = REPLACE(post_content, '”', '”');
UPDATE wp_posts SET post_content = REPLACE(post_content, '’', '’');
UPDATE wp_posts SET post_content = REPLACE(post_content, '‘', '‘');
UPDATE wp_posts SET post_content = REPLACE(post_content, '—', '–');
UPDATE wp_posts SET post_content = REPLACE(post_content, '–', '—');
UPDATE wp_posts SET post_content = REPLACE(post_content, '•', '-');
UPDATE wp_posts SET post_content = REPLACE(post_content, '…', '…');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '“', '“');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '”', '”');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '’', '’');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '‘', '‘');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '—', '–');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '–', '—');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '•', '-');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '…', '…');

Deactivate all plugins:

UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins';

Quick search and replace content in blog posts:

UPDATE wp_posts SET 'post_content' = REPLACE ('post_content', 'Old Content', 'New Content');

Btw, don’t try this if you don’t know how to use SQL queries.

Written on September 7, 2017