CODEWITHSUNDEEP
mysqldatabasedatetime
onestepcreative
onestepcreative

Reputation: 561

Change Value in MySQL Table Based on Date-Range

I'm trying to change the status of a post from 'Inherit' to 'Published', but only for posts with dates between 08/01/2012 & 10/08/2012. Would this be the proper query to run to achieve this?

UPDATE wp_posts set post_status = replace(post_content, 'inherit', 'published') WHERE post_date BETWEEN '2012/08/01 00:00:00.000' AND '2012/10/08 23:59:00.000'

Upvotes: 1

Views: 845

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270609

If post_status is a single value, you needn't do a REPLACE() on it. Just set it to the new value. And MySQL dates should be formatted as YYYY-MM-DD HH:ii:ss as in 2012-08-01 00:00:00

UPDATE
  wp_posts
/* Set to the new status */
SET post_status = 'published'
WHERE
  /* Include the old status in the WHERE clause */
  post_status = 'inherit'
  AND post_date BETWEEN '2012-08-01 00:00:00' AND '2012-10-08 23:59:00'

The REPLACE() method, although it would work, would likely result in a much slower query since the replacement operation would take place in every row, but only actually have an effect on rows which included the string inherit.

Upvotes: 1

Related Questions