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, 'staging.site.net', 'site.net');
UPDATE wp_posts SET post_content = replace(post_content, 'staging.site.net', 'site.net');
UPDATE wp_posts SET guid = replace(guid, 'staging.site.net', 'site.net');
UPDATE wp_postmeta SET meta_value = replace(meta_value, 'staging.site.net', 'site.net');
UPDATE wp_usermeta SET meta_value = replace(meta_value, 'staging.site.net', 'site.net');

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.