Useful WordPress SQL queries

Useful WordPress SQL queries

The best Database Editor is phpMyAdmin as for me. It is very simple and comfortable. To run SQL queries below on database choose a required database in the left sidebar and open SQL tab in the top of the right window.

How to remove all posts with a custom ‘post type’ and their meta fields in DB:

DELETE FROM wp_posts WHERE post_type='post_type';
DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT id FROM wp_posts);
DELETE FROM wp_term_relationships WHERE object_id NOT IN (SELECT id FROM wp_posts)

How to replace old site domain with new one in DB:

UPDATE wp_options SET option_value = replace(option_value, '', '');
UPDATE wp_posts SET post_content = replace(post_content, '', '');
UPDATE wp_posts SET guid = replace(guid, '', '');
UPDATE wp_postmeta SET meta_value = replace(meta_value, '', '');
UPDATE wp_usermeta SET meta_value = replace(meta_value, '', '');

How to delete SPAM acounts in database if they have some similar substring in their name:

DELETE FROM wp_users WHERE display_name LIKE '%Pretty Maria%';
DELETE FROM wp_usermeta WHERE user_id NOT IN (SELECT ID FROM wp_users);
DELETE FROM wp_bp_xprofile_data WHERE user_id NOT IN (SELECT ID FROM wp_users);

In the code above an SPAM user has “Pretty Maria” substring in his name like “Pretty Maria RUB Tinkoff”. I deleted about 2k users in such way. Also, this SQL queries delete the users from BuddyPress`s table too.

NOTE. Do not forget to change the table prefix ‘wp_’ if you have another in your database.

P.S. This article will be updated with new code periodically.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.