skip to Main Content
Menu

Most Important WordPress SQL Queries

To Add A Custom Field To All Articles And Pages:

If you want to add a customized field to all the articles and pages then you need to replace the ‘universalCustomfield’ with the replaced name and then replace the ‘myvalue’ to the value of your choice.

Adding a well customized field in the pages and articles.

This query will help you in customizing the particular fields and articles.

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID AS post_id, ‘OwnCustomField’
AS meta_key ‘myvalue AS meta_value FROM wp_posts
WHERE ID NOT IN (SELECT post_id FROM wp_postmeta WHERE meta_key = ‘MonCustomField’);

Add a Custom Field in Articles Only

Now talking about the customization in articles only then you can run the below written query. By this you will be able to customize the article field.

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID AS post_id, ‘MyCustomField’
AS meta_key ‘myvalue AS meta_value
FROM wp_posts WHERE ID NOT IN
(SELECT post_id FROM wp_postmeta WHERE meta_key = ‘MonCustomField’)
“ AND post_type = ‘post’;

Adding a Custom Field in only Pages

This query will help you in particular customization of pages only. You can run the below written query.

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID AS post_id, ‘MyCustomField’
AS meta_key ‘myvalue AS meta_value
FROM wp_posts WHERE ID NOT IN
(SELECT post_id FROM wp_postmeta WHERE meta_key = ‘MonCustomField’)
AND `post_type` = ‘page’;

How to Update an Email Address with the WordPress Database:

Here mentioning another good example of replace function by which you can add Email Address in the WordPress database. The below written query will replace the email address that has been mentioned in the comment field with a fresh one.

UPDATE`wp_comments`SET`comment_author_email`= REPLACE(comment_author_email,’old-email@address.com’,’new- email@address.com’ );

Now Deleting All the Spam Comment

When we talk about deleting the spam comments then this small snippet are the life saver. This will remove all your spam comments by just running the below mentioned query.

DELETE FROM wp_comments WHERE wp_comments.comment_approved = ‘spam’;

Deleting All Unapproved Comments

When you want to remove all your unapproved comments from the blog the then this SQL query does the magic. It will delete only the unapproved comment and would not touch your approved comments.

DELETE FROM wp_comments WHERE comment_approved = 0;

Disable Comments on Older Posts

The another important SQL query for disabling the comments for older posts then you should mention the comment_status as open, closed, or registered_only. This will help you disabling the comments from the older posts.

UPDATE wp_posts SET comment_status =’closed’ WHERE post_dateEnabling and Disabling Of Trackbacks and Pingbacks

If you want to enable the trackback or disable the trackback then you need to just mention the status of the comment as open, closed or registered. This will enable and disable your pingbacks.

For globally enabling of pingbacks/trackbacks for all users:

UPDATE wp_posts SET ping_status = ‘open’;

For globally disabling of pingbacks/trackbacks for all users:

UPDATE wp_posts SET ping_status = ‘closed’;

Enabling and disabling of Trackbacks before a certain date:

This query will help you in disabling the trackback from a particular date, you just need to specify the ping status as open/ close. You can even mention the date by which the editing is to be done that need to be enable or disabled.

UPDATE wp_posts SET ping_status = ‘closed’ WHERE post_date < ’2010-01-01′ AND post_status = ‘publish’; Identify and delete posts that are ‘X’ days old This query will help you to identify and delete the post from any particular day. Here you can mention the days like ‘X’ days old etc. when you need to identify the post that how much day it is old then simply run this query and remember to replace the ‘X’ with the number of days you are looking for. SELECT*FROM`wp_posts`WHERE`post_type`=’post’ANDDATEDIFF(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 short codes:

Everyone knows that the shortcodes of wordpress is excellent but sometime you need to get rid of the unwanted codes then you can use this query.

UPDATE wp_post SET post_content =replace(post_content, ‘[tweet]‘, ” ) ;

Changing the WordPress post into pages and changing the pages into post

By this query you can change the word press pages into post and can convert the post into pages. You just need to run this below query and rest it will do. Remember the query will run through PHPMyAdmin

UPDATE wp_posts SET post_type = ‘page’ WHERE post_type = ‘post’.

Change author attribution on all posts at once.

If you want to convert the author attributes on every post by once then you need to run this query and it will retrieve the IDs of the word press author.

SELECT ID, display_name FROM wp_users;

And once you have run this query you will retrieve an old and new query. Afterwards you can run the below given query but just remember to change the new author id with the new retrieved id and old id with the old one.

UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;

Deleting the post revisions

From this query you will be able to delete the posts revision.

DELETE FROM wp_posts WHERE post_type =”revision”;

Enabling and Disabling of WordPress plugins through the database.

Now if you wish to enable and disable the features of wordpress plug-in then first of all login to the wordpress admin and then activate the required plug in then this below given query will help you in enabling and disabling the plug-in instantly.

UPDATE wp_options SET option_value = ‘a:0:{}’ WHERE option_name = ‘active_plugins’;

Changing the URL destination of a WordPress

Now if you need to change the URL of the desired destination in a wordpress then you need to run the below given query.

UPDATE wp_options SEToption_value=replace(option_value,’http://www.old-site.com’,’http://www.new-site.com’) WHERE option_name =’home’ORoption_name = ‘siteurl’;

Then you will have to change the URL from the table wp_posts with this snippet:

UPDATE wp_posts SETguid=replace(guid,’http://www.old-site.com’,’http://www.new-site.com);

And finally you want to your content of the desired post then you can do this perfectly but try to keep the old URL link.

UPDATE wp_posts SET post_content=replace(post_content,’ http://www.ancien-site.com ‘, ‘ http://www.nouveau-site.com ‘);

How to reset WordPress password manually

Here you can reset you password manually in the WordPress just by running this SQL query.

UPDATE `wordpress`.` wp_users ` SET `user_pass’=MD5(‘PASSWORD’)WHERE `wp_users`.`user_login` =`admin` LIMIT 1;
Searching and then replace the content in the post

To change the original Text that you want to replace with the Replace Text option by just running the search and replace query mentioned below.

UPDATE wp_posts SET `post_content` = REPLACE (`post_content`, ‘OriginalText’, ‘ReplacedText’);

 

Source : http://www.webgranth.com/15-most-important-wordpress-sql-queries-highly-beneficial

Alphonse

Editorial Staff at ETN Multimedia is led by Alphonse Tan. Page maintained by Alphonse Tan

Back To Top