Skip to content

Instantly share code, notes, and snippets.

@mlbd
Last active September 26, 2019 06:29
Show Gist options
  • Save mlbd/c34b34a108394908cc1be2d61c847322 to your computer and use it in GitHub Desktop.
Save mlbd/c34b34a108394908cc1be2d61c847322 to your computer and use it in GitHub Desktop.
/**
* 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