Wordpress queries for Migrate sites

gistfile1.sql

#CHANGE SITEURL & HOMEURL
UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldsiteurl.com', 'http://www.newsiteurl.com') WHERE option_name = 'home' OR option_name = 'siteurl';


#CHANGE GUID
UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.oldsiteurl.com', 'http://www.newsiteurl.com');

# CHANGE URL IN CONTENT
UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://www.oldsiteurl.com', 'http://www.newsiteurl.com');

# CHANGE IMAGE PATH ONLY
UPDATE wp_posts SET post_content = REPLACE (post_content, 'src="http://www.oldsiteurl.com', 'src="http://yourcdn.newsiteurl.com');

UPDATE wp_posts SET  guid = REPLACE (guid, 'http://www.oldsiteurl.com', 'http://yourcdn.newsiteurl.com') WHERE post_type = 'attachment';

# UPDATE POST META
UPDATE wp_postmeta SET meta_value = REPLACE (meta_value, 'http://www.oldsiteurl.com','http://www.newsiteurl.com');


# CHANGE DEFAULT "ADMIN" USERNAME
UPDATE wp_users SET user_login = 'Your New Username' WHERE user_login = 'Admin';

# RESET PASSWORD
UPDATE wp_users SET user_pass = MD5( 'new_password' ) WHERE user_login = 'your-username';

# ASSIGN ALL ARTICLES BY AUTHOR B TO AUTHOR A
UPDATE wp_posts SET post_author = 'new-author-id' WHERE post_author = 'old-author-id';

# DELETE REVISIONS
DELETE a,b,c FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision'

# EXPORT ALL COMMENT EMAILS WITH NO DUPLICATE
SELECT DISTINCT comment_author_email FROM wp_comments;


# DELETE ALL SPAM COMMENTS
DELETE FROM wp_comments WHERE comment_approved = 'spam';

0 = Comment Awaiting Moderation
1 = Approved Comment
spam = Comment marked as Spam

Inpired on
http://www.onextrapixel.com/2010/01/30/13-useful-wordpress-sql-queries-you-wish-you-knew-earlier/