https://www.wpkube.com/work-wordpress-database-11-useful-sql-queries/
- Change Your WordPress Password
Forgotten your password and locked yourself out of your WordPress site? No worries – you can reset it manually in the database with this SQL query:
UPDATE wp_users SET user_pass = MD5( '[new_password]' ) WHERE user_login = '[username]';
MD5 encrypts the new password so anyone gaining access to the database is not able to read the password.
- Transfer Posts to a New Author
If you have multiple authors on a site or take over an existing site from another owner, you might want to transfer existing posts into your username. To do this you can use the following query:
UPDATE
wp_posts
SET
post_author = (SELECT ID FROM wp_users WHERE user_login = '[new_author_login]')
WHERE
post_author = (SELECT ID FROM wp_users WHERE user_login = '[old_author_login]');
- Bulk Delete Spam Comments
WordPress comes with the Akismet plugin installed as default, which is pretty good at flagging spam. However it’s up to you to regularly check and delete your spam messages.
If you’ve left it a while, you could have thousands of spam messages and it would take ages to delete them all from the dashboard one page at a time. Instead use the following SQL query to delete the lot in one go:
DELETE FROM wp_comments WHERE comment_approved = "spam";
If you have thousands of pending comments and don’t have time to renew them, you can just delete them with the following query (but be aware you may end up deleting some genuine comments):
DELETE FROM wp_comments WHERE comment_approved = "0";
If you don’t want to delete all your comments but have a problem with a spammer who keeps linking back to a certain website, you can use this query to get rid of all comments attributed to a certain URL:
DELETE from wp_comments WHERE comment_author_url LIKE "%spamurl%" ;
- Update Links When You Move to a New Domain
Links in WordPress are absolute rather than relative, so you’ll have to change them all when you move your site to a new domain. There are plugins that will do this for you or your can use the following SQL query:
UPDATE wp_posts SET post_content = REPLACE (post_content, '[old_domain]', '[new_domain]');
This is basically a find and replace function for all the posts on your site so you may be able to use it for other purposes too.
- Change Your WordPress Posts into Pages
Sometimes you create a post in WordPress and realize at a later date that it’s a resource that would probably be better as a page. Luckily this is really easy to change:
UPDATE wp_posts SET post_type = 'page' WHERE post_type = 'post';
You can also change pages into posts:
UPDATE wp_posts SET post_type = 'post' WHERE post_type = 'page';
- Change your WordPress Username
You can’t change your WordPress username from within the dashboard, which is a bit of a pain. Thankfully it’s straightforward to do with SQL. You should seriously consider doing this if your admin username is “Admin”, to strengthen the security of your site.
UPDATE wp_users SET user_login = 'newusername' WHERE user_login = 'oldusername';
- Delete Post Revisions
WordPress automatically saves different revisions of your posts as you edit them but these can take up a lot of space in your database. You can delete them with the following query:
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';
- Disable Comments on Old Posts
Spammers often target old posts. You can turn off comments on all posts published before a certain date with this query (edit the date to one of your choosing)
UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2016-01-01' AND post_status = 'publish';
- Change URL of WordPress Images
If you’ve moved your WordPress site, you’ll need to change all your image URLs. You might also decide to host your images elsewhere to save on server space. To change your image URLs use the following query:
UPDATE wp_posts SET post_content = replace(post_content, 'Old URL', 'New URL');
- Batch Disable Plugins
Most technical issues with your WordPress site can be traced back to a rogue plugin. If you can’t get into your admin panel, try disabling all plugins from your database first and you can then go in and re-enable them one by one.
UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins';
- Disable Comments on All Posts
Undergoing a spam attack? Or just had enough of other people’s opinions? You can turn off comments on all posts at once with this query:
UPDATE wp_posts SET comment_status = 'closed' where post_type ='post';