[Useful SQL queries] #wp #sql

useful-sql-queries.sql

/* remove meta field */
DELETE FROM wp_postmeta WHERE meta_key = 'YourMetaKey';

/* batch deleted spam comments */
DELETE FROM wp_comments WHERE wp_comments.comment_approved = 'spam';

/* batch deleted all unapproved comments */
DELETE FROM wp_comments WHERE comment_approved = 0;

/* disable comments on older posts */
UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2016-01-01' AND post_status = 'publish';

/* remove unwanted shortcodes */
UPDATE wp_post SET post_content = replace(post_content, '[unusedshortcodes]', '' );

/* change post type */
UPDATE wp_posts SET post_type = 'page' WHERE post_type = 'post';

/* delete all revisions */
DELETE FROM wp_posts WHERE post_type = "revision";

/* search and replace post content */
UPDATE wp_posts SET 'post_content' = REPLACE ('post_content', 'OriginalText', 'NewText');

/* delete all WooCommerce products */
DELETE relations.*, taxes.*, terms.*
FROM wp_term_relationships AS relations
INNER JOIN wp_term_taxonomy AS taxes
ON relations.term_taxonomy_id=taxes.term_taxonomy_id
INNER JOIN wp_terms AS terms
ON taxes.term_id=terms.term_id
WHERE object_id IN (SELECT ID FROM wp_posts WHERE post_type='product');

DELETE FROM wp_postmeta WHERE post_id IN (SELECT ID FROM wp_posts WHERE post_type = 'product');
DELETE FROM wp_posts WHERE post_type = 'product';