Change the date of WordPress posts in an SQL Mass Update

When I programatically added most of the blog posts in this blog, I forgot to adjust the date and time of each post to its actual age. So I decided to set it via SQL directly in the database.

First I worked out a SELECT query to filter all the entries I want to change:

SELECT *
FROM `wp_posts`,`wp_term_relationships`,`wp_terms`
WHERE wp_posts.id=wp_term_relationships.object_id
AND wp_term_relationships.term_taxonomy_id=wp_terms.term_id
AND wp_terms.name='Ubuntu 8.04'
AND post_date BETWEEN '2010-12-18 00:00:00' AND '2010-12-18 10:00:00'
LIMIT 0 , 100

The WHERE statements are the most interesting. They mean:

  • The first = comparisons are like an INNER JOIN (check this for more information). I connect all the foreign keys together.
    wp_posts is the table with the blog posts, wp_terms the table with containing the tag names and wp_term_relationships connects wp_posts with wp_terms. So we want all posts where the tag is equal to 'Ubuntu 8.04'.
  • Additionally we only select posts that have a post_date between 0:00a.m. and 10a.m. on the same day (2010-12-18).

Now we want to update those posts to a date that matches their original creation date better than the current one.

UPDATE `wp_posts`,`wp_term_relationships`,`wp_terms`
SET
`wp_posts`.`post_date`='2008-10-18 10:18:00',
`wp_posts`.`post_date_gmt`='2008-10-18 10:18:00'
WHERE wp_posts.id=wp_term_relationships.object_id
AND wp_term_relationships.term_taxonomy_id=wp_terms.term_id
AND wp_terms.name='Ubuntu 8.04'
AND post_date BETWEEN '2010-12-18 00:00:00' AND '2010-12-18 10:00:00'

Comments