I sent a code snippet earlier today and realized. Duh. I should be blogging this.
Here is some code that I have used to update the guid in my wp_posts table. Replace ‘example.com’ with whatever your base url is for your WordPress installation.
Two update queries, one for posts and one for pages. This only updates published posts and ones which don’t have the guid set yet (remove the guid = ” line to replace existing guid values).
update wp_posts
set guid = concat('http://www.example.com/?p=',ID)
where guid = ''
and post_status = 'publish'
and post_type = 'post';
update wp_posts
set guid = concat('http://www.example.com/?post_id=',ID)
where guid = ''
and post_status = 'publish'
and post_type = 'page';
Update: Also Change Your Site URL in Posts in SQL
I have recently been using a different method when moving sites to update the guid and also to do a search and replace within posts and pages for the url of your site. This is necessary when changing your site url since uploaded image references in your posts are set to the absolute url.
/* Use this to update the guid url */ UPDATE wp_posts SET guid = REPLACE ( guid, 'http://www.example-old-url.com', 'http://www.example-new-url.com'); /* Use this to update urls in posts, good for images! */ UPDATE wp_posts SET post_content = REPLACE ( post_content, 'http://www.example-old-url.com', 'http://www.example-new-url.com');
{ 8 comments… read them below or add one }
You freaking ROCK. You just saved me hours and hours of madness after moving posts from one database to a new one — while on the same domain, which meant I couldn’t use existing plugins for domain db transfers. One simple query and tadah, all straightened out. I shall now dance around my office in glee at having twenty spare minutes, in joyous gratitude! Thank you for this!
I’m glad I could help kaigo. I just updated this post with more info on updating your url in the database by doing a search and replace in mySQL. I’ve found that method to be very handly.
I could be wrong, but shouldn’t both the old and new URLs both either have the slash or not… like this:
‘http://www.example-old-url.com’,
‘http://www.example-new-url.com’);
instead of this:
‘http://www.example-old-url.com/’,
‘http://www.example-new-url.com’);
otherwise, that slash will get erased, and you could get a URL like:
http://www.example-new-url.comblog/
when you really want:
http://www.example-new-url.com/blog/
You are absolutely correct Matt. I have fixed the typo in the code.
Hi there, thank you so much for this. help me bigtime!
One comment, your inital code block said
set guid = concat(‘http://www.example.com/?post_id=’,ID)
whereas maybe it should be
set guid = concat(‘http://www.example.com/?paget_id=’,ID)
Thanks again though!
Sorry for my typo!
set guid = concat(‘http://www.example.com/?page_id=’,ID)
Where can you add this code in the source files of WordPress so that all new messages have the correct guid-link in the wp_post table?
Thanks a lot – saved me from all kinds of hell…