SQL queries for WordPress

Changing the user password:

UPDATE wp_users SET user_pass = MD5('password') WHERE user_login = 'name';
UPDATE wp_users SET user_pass = MD5('password') WHERE ID=1;

Changing the user login:

UPDATE wp_users SET user_login='name' WHERE user_login='name';

Building a list of email commenters:

SELECT DISTINCT comment_author_email FROM wp_comments;

Disable comments for all posts:

UPDATE wp_posts SET comment_status = 'closed';

Enable comments for all posts:

UPDATE wp_posts SET comment_status = 'open';

Enable comments for all posts for registered users only:

UPDATE wp_posts SET comment_status = 'registered_only';

Delete all spam comments:

DELETE FROM wp_comments WHERE comment_approved = 'spam';

Delete all unapproved comments:

DELETE FROM wp_comments WHERE comment_approved = 0;

Clear URL field values for all comments:

UPDATE wp_comments SET comment_author_url='';

Closing comments in old posts:

UPDATE wp_posts SET comment_status = 'closed'
WHERE post_date < '2014-01-01' AND post_status = 'publish';

Closing of comments in a certain category:

UPDATE wp_posts p
LEFT JOIN wp_term_relationships rel ON ( p.ID = rel.object_id )
LEFT JOIN wp_term_taxonomy tax ON ( tax.term_taxonomy_id = rel.term_taxonomy_id  )
LEFT JOIN wp_terms tm ON ( tm.term_id = tax.term_id )
SET p.comment_status = 'closed' WHERE tm.slug = 'category_name';

Removing comments in the url of the following characters:

DELETE a,b FROM wp_comments a
LEFT JOIN wp_commentmeta b ON ( a.comment_ID = b.comment_id )
WHERE a.comment_author_url LIKE '%characters%';

Bulk editing of the commentor url:

UPDATE wp_comments SET comment_author_url = 'http://new_address'
WHERE comment_author = 'admin' AND comment_author_email = 'admin@example.com';

Another version of the massive change of url commentators:

UPDATE wp_comments
SET comment_author_url = REPLACE( comment_author_url, 'old_address', 'new_address' );

Bulk edit email comment:

UPDATE wp_comments SET comment_author_email = REPLACE ( comment_author_email, 'old@example.com', 'new@example.com' );

Closing pings:

UPDATE wp_posts p SET p.ping_status = 'closed';

Opening pings:

UPDATE wp_posts p SET p.ping_status = 'open';

Delete all comments from pings:

DELETE FROM wp_comments WHERE comment_type = 'pingback';

Changing a specific website address or any expression in all posts to another:

UPDATE wp_posts
SET post_content = REPLACE (post_content, 'http://OLD', 'http://NEW');

Mass change of the author of records:

UPDATE wp_posts SET post_author=1 WHERE post_author=2;
UPDATE wp_posts SET post_author=1 WHERE post_author=16 AND post_modified<'2014-11-17 00:00:00';

Deleting record revisions:

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';

Deactivate all plugins:

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

Clearing the feed cache:

DELETE FROM `wp_options`
WHERE `option_name` LIKE ('_transient%_feed_%');

Leave a Reply