SQL Statements

I’m always digging back to find old notes on SQL statements, so I’m putting this page here as a place to store them all.

DELETE FROM wp_postmeta WHERE meta_key = 'meta_desc';
UPDATE `wp_postmeta` SET `meta_key` = 'dwp23_image_meta_url' WHERE `meta_key` = 'featured_image_url';
UPDATE `wp_postmeta` SET `meta_key` = 'dwp23_media_meta_url' WHERE `meta_key` = 'media';
// common injection hack
SELECT * FROM wp_posts WHERE post_content LIKE '%display: none%';
DELETE FROM wp_posts WHERE post_type = "revision";
DELETE FROM wp_comments WHERE comment_approved = 'spam';
DELETE FROM wp_options WHERE option_name LIKE ('_transient%_feed_%');
DELETE a,b,c FROM wp_posts a WHERE a.post_type = 'revision' LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id);
DELETE FROM wp_usermeta WHERE NOT EXISTS ( SELECT * FROM wp_users WHERE wp_usermeta.user_id = wp_users.ID )
DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);
DELETE FROM wp_term_taxonomy WHERE term_id not IN (SELECT term_id FROM wp_terms);
DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0 );
UPDATE wp_posts SET ping_status = 'closed';
ALTER TABLE wp_comments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE wp_comments ENGINE = InnoDB;