Last active
September 26, 2019 06:29
-
-
Save mlbd/c34b34a108394908cc1be2d61c847322 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* First of all change prefix if you have different one. As default WordPress has `wp_` | |
*/ | |
# Update post meta to another meta key | |
# Transfer one post meta to another meta key | |
UPDATE wp_postmeta SET meta_key = 'new_key_name' WHERE meta_key = 'old_key_name'; | |
// Update home and siteurl. | |
UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldurl', 'http://www.newurl') WHERE option_name = 'home' OR option_name = 'siteurl'; | |
// Update posts guid. | |
UPDATE wp_posts SET guid = replace(guid, 'http://www.oldurl','http://www.newurl'); | |
// Update post contents. | |
UPDATE wp_posts SET post_content = replace(post_content, 'http://www.oldurl', 'http://www.newurl'); | |
// Update postmeta. | |
UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://www.oldurl','http://www.newurl'); | |
// Transfer usermeta to new key. | |
UPDATE wp_usermeta SET meta_key = 'new_key_name' WHERE meta_key = 'old_key_name'; | |
// Delete Orphaned Post Meta Data in WordPress | |
SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL; | |
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL; | |
// Update postmeta specific key and value. | |
UPDATE wp_postmeta SET meta_value = replace(meta_value, 'old_value', 'new_value') WHERE `meta_key` LIKE 'your_key'; | |
// Delete all user at once except specific user_id | |
DELETE FROM wp_users WHERE ID NOT in (1,2); | |
// Delete huge amount of usermeta at once except specific user_id | |
DELETE FROM wp_usermeta WHERE user_id NOT in (1,2) and user_id < 1000 | |
DELETE FROM wp_usermeta WHERE user_id NOT in (1,2) and user_id < 2000 | |
DELETE FROM wp_usermeta WHERE user_id NOT in (1,2) and user_id < 3000 | |
// Delete Orphaned User Meta Data in WordPress | |
DELETE FROM wp_usermeta WHERE NOT EXISTS (SELECT * FROM wp_users WHERE wp_usermeta.user_id = wp_users.ID); | |
// Delete Specific post type posts and those posts postmeta, taxonomies | |
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); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment