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

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.