helpful SQL commands.sql

	/* CHANGE URL IN DB */
	/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	~~~~~~~~~~USE THIS ONE IN PHPMYADMIN ~~~~~~~~~~~~~~~
	~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
	UPDATE wp_posts set post_content=REPLACE(post_content, 'www.devServer.com/', 'www.liveServer.com/');  

	/* CUSTOM FIELDS INCLUDED */
	UPDATE wp_postmeta SET meta_value= replace(meta_value, 'http://old-domain.com', 'http://new-domain.com');


	/* CHANGE ALL URLS WORDPRESS */
	UPDATE wp_options SET option_value = replace(option_value, 'http://www.old-domain.com', 'http://www.new-domain.com') WHERE option_name = 'home' OR option_name = 'siteurl';



	/* example line to fix links */
	UPDATE wp_postmeta SET meta_value= replace(meta_value, 'http://gadentalassisting.com/wordpress/', 'http://gadentalassisting.com');

	/* example line to point wp-admin to right area and change sitehome */
	UPDATE wp_options SET option_value = replace(option_value, 'http://gadentalassisting.com/wordpress/', 'http://gadentalassisting.com') WHERE option_name = 'home' OR option_name = 'siteurl';







	/* ===========================================
		ERASE ALL 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';
	
	
	/* ===========================================
		Add a Custom Field to All Posts & Pages
		
		This snippet will add a custom field to every post and page found in your WP database. 
		All you have to do is replace the UniversalCutomField to whatever Custom Field name 
		you like to create, and then change MyCustomFieldValue to the value of your choice.
	=============================================*/
	
	INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
	SELECT ID AS post_id, 'UniversalCustomField'
	AS meta_key 'MyCustomFieldValue' AS meta_value FROM wp_posts
	WHERE ID NOT IN (SELECT post_id FROM wp_postmeta WHERE meta_key = 'UniversalCustomField');
	
	/* For posts only, use this snippet… */
	
	INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
	SELECT ID AS post_id, 'UniversalCustomField'
	AS meta_key 'MyCustomFieldValue' AS meta_value
	FROM wp_posts WHERE ID NOT IN
	(SELECT post_id FROM wp_postmeta WHERE meta_key = 'UniversalCustomField')
	'' AND post_type = 'post';
	
	/* …and for pages only, use this code… */
	
	INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
	SELECT ID AS post_id, 'UniversalCustomField'
	AS meta_key 'MyCustomFieldValue' AS meta_value
	FROM wp_posts WHERE ID NOT IN
	(SELECT post_id FROM wp_postmeta WHERE meta_key = 'UniversalCustomField')
	AND 'post_type' = 'page';
	
	
	/* ===========================================
		Delete Post Meta
		
		When you install or remove plugins they make use of the post meta to store data. 
		After you have removed a plugin, the data will remain in the post_meta table, which
		of course, is no longer needed. Remember and change YourMetaKey to your own 
		value before running this query.
		
	=============================================*/
	
	DELETE FROM wp_postmeta WHERE meta_key = 'YourMetaKey';
	
	/* ===========================================
		Identify Unused Tags
		
		In a WordPress database, if you run a query to delete old posts, like the one above, 
		the old tags will remain. This query allows you to identify all of the unused tags.
		
	=============================================*/
	SELECT * From wp_terms wt
	INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy='post_tag' AND wtt.count=0;
	
	/* ===========================================
		Batch Deleting Spam Comments
		
		This little snippet is a life-saver. All you have to do to delete them all is run this SQL command:
	=============================================*/
	DELETE FROM wp_comments WHERE wp_comments.comment_approved = 'spam';

	/* ===========================================
		Batch Deleting 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';
	
	/* ===========================================
		Disabling and Enabling Trackbacks & Pingbacks
		
		For this query, specify the comment_status as either open, closed, or registered_only.
	=============================================*/
	
	/* Globally enable pingbacks/trackbacks for all users: */
	UPDATE wp_posts SET ping_status = 'open';
	
	/* Globally disable pingbacks/trackbacks for all users:*/
	UPDATE wp_posts SET ping_status = 'closed';
	
	/* For this query, specify the ping_status as either open or closed. Also, specify the date by editing the 2016-01-01 to suit your needs. */
	UPDATE wp_posts SET ping_status = 'closed' WHERE post_date < '2016-01-01' AND post_status = 'publish';
	
	/* ===========================================
		Delete Comments With a Specific URL
		
		If you have spam comments that all contain the same URL, then this query allows 
		you to remove them in one go. The following query will delete all comments with a 
		particular URL. The '%’ means that any URL containing the string within the '%’ signs will be deleted.
	=============================================*/
	DELETE from wp_comments WHERE comment_author_url LIKE "%nastyspamurl%" ;	
	
	/* ===========================================
		Identify & Delete Posts that are over 'X' Days Old
		
		If you ever need to identify and delete posts that are over a certain amount of days old, then this 
		snippet will help.
		
		To identify any posts that are over 'X’ amount of days run this query, remembering to replace 
		the 'X' with the number of days you are looking for:
	=============================================*/
	SELECT * FROM 'wp_posts'
	WHERE 'post_type' = 'post'
	AND DATEDIFF(NOW(), 'post_date') > X

	/* To delete any posts that are over 'X' amount of days run this query: */
	DELETE FROM 'wp_posts'
	WHERE 'post_type' = 'post'
	AND DATEDIFF(NOW(), 'post_date') > X
	
	/* ===========================================
		Removing Unwanted Shortcodes
		
		WordPress shortcodes are great, but if you decide to stop using them, their code will stay within 
		your post content. Here is a simple SQL query to run on your database to get rid of any unwanted 
		shortcodes. Replace unusedshortcodes with your own shortcode name.
	=============================================*/
	UPDATE wp_post SET post_content = replace(post_content, '[unusedshortcodes]', '' );
	
	
	/* ===========================================
		Change Your WordPress Posts Into Pages and Vice-Versa
		
		Changing posts to pages is very easy, all you have to do is run this short SQL query:
	=============================================*/
	UPDATE wp_posts SET post_type = 'page' WHERE post_type = 'post'
	
	/* … and if you want to change pages to posts use this snippet: */
	UPDATE wp_posts SET post_type = 'post' WHERE post_type = 'page'
	
	/* ===========================================
		Change Author Attribution On All Posts
		
		The first thing you will need to do for this snippet is retrieve the IDs of the WordPress author. 
		You can find this out by using the following SQL command:
	=============================================*/
	SELECT ID, display_name FROM wp_users;
	
	/* Once you have the old and new IDs, insert the command below, remembering to replace NEW_AUTHOR_ID 
	with the new authors ID and OLD_AUTHOR_ID with the old. */
	UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;
	
	/* ===========================================
		Batch Deleting Post Revisions
	=============================================*/
	DELETE FROM wp_posts WHERE post_type = "revision";
	
	/* ===========================================
		Disable or Enable All WordPress Plugins
		
		If you have ever encountered the white screen of death and found yourself unable to login to the 
		WordPress Admin after activating a new plugin, then this snippet will certainly help you. It will disable
		all plugins instantly, allowing you to log back in.
	=============================================*/
	UPDATE wp_options SET option_value = 'a:0:{}' WHERE option_name = 'active_plugins';
	
	
	/* ===========================================
		Changing the Destination URL of a WordPress Site
		
		Once you’ve moved your blog (template files, uploads & database) from one server to another, the next thing you will then need to do is to tell WordPress your new address.
	=============================================*/
	/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
		Remember and change http://www.old-site.com to your old URL, and the http://www.new-site.com to your new URL. 
	!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!*/
	/* The first command to use is: */
	UPDATE wp_options SET option_value = replace(option_value, 'http://www.old-site.com', 'http://www.new-site.com') WHERE option_name = 'home' OR option_name = 'siteurl';
	
	/* Then you will have to change the url from the table wp_posts with this snippet: */
	UPDATE wp_posts SET guid = replace(guid, 'http://www.old-site.com','http://www.new-site.com');
	
	/* And finally, you'll need to do a search through the content of your posts to be sure that your new URL link is not messing with the old URL: */
	UPDATE wp_posts SET post_content = replace(post_content, ' http://www.ancien-site.com ', ' http://www.nouveau-site.com ');
	
	
	/* ===========================================
		Change the Default 'Admin' Username
		
		Every WordPress installation will create an account with the default Admin username. Being able to change this default username, 
		will give your WordPress admin panel additional security.
		
		Change YourNewUsername to your new name:
	=============================================*/
	UPDATE wp_users SET user_login = 'YourNewUsername' WHERE user_login = 'Admin';
	
	
	/* ===========================================
		Manually Reset your WordPress Password
		
		If you've only a single user on your WordPress installation, and the login name is 'admin', you can reset your 
		password with this simple SQL query. Once executed, it will replace PASSWORD with your new password.
	=============================================*/
	UPDATE 'wordpress'.'wp_users' SET 'user_pass' = MD5('PASSWORD') WHERE 'wp_users'.'user_login' ='admin' LIMIT 1;
	
	
	/* ===========================================
		Search and Replace Post Content
		
		To search and replace post content, use the following code. Replace OriginalText with the current text and replace NewText with your new text.
	=============================================*/
	UPDATE wp_posts SET 'post_content'
	= REPLACE ('post_content',
	'OriginalText',
	'NewText');
	
	
	/* ===========================================
		Changing the URL of Images
		
		If you need to change the paths of your images, you can use this SQL command:
	=============================================*/
	UPDATE wp_posts
	SET post_content = REPLACE (post_content, 'src=”http://www.myoldurl.com', 'src=”http://www.mynewurl.com');